Re: select into composite type / return - Mailing list pgsql-sql

I now have the working functions.

The first accepts 7 arguments and returns  a composite type of the
calculations breakdown.
The second takes a single argument and retrieves the 7 arguments from a
table before calling the first argument.

What I can't get my head round is how I can use these functions to
return a setof breakdowns. All I can get is thebreakdown returned as a
single column.

All advice welcome.

users=# select * from do_breakdown(1);
   f1  |  f2  |  f3  |  f4  |  f5  |  f6
------+------+------+------+------+------
  1.00 | 2.00 | 3.00 | 4.00 | 5.00 | 6.00
(1 row)

users=# select * from sessions;
  id |  v1   |  v2   |  v3   |  v4   |  v5   |  v6   |  v7
----+-------+-------+-------+-------+-------+-------+-------
   1 |  1.00 |  2.00 |  3.00 |  4.00 |  5.00 |  6.00 |  7.00
   2 | 11.00 | 12.00 | 13.00 | 14.00 | 15.00 | 16.00 | 17.00
   3 | 21.00 | 22.00 | 23.00 | 24.00 | 25.00 | 26.00 | 27.00
(3 rows)

users=# select id, do_breakdown(id) from sessions where id in (1,3);
  id |             do_breakdown
----+---------------------------------------
   1 | (1.00,2.00,3.00,4.00,5.00,6.00)
   3 | (21.00,22.00,23.00,24.00,25.00,26.00)
(2 rows)

users=#


create type breakdown as (
    f1    numeric(9,2),
    f2    numeric(9,2),
    f3    numeric(9,2),
    f4    numeric(9,2),
    f5    numeric(9,2),
    f6    numeric(9,2)
);

create table sessions (
    ID int4 not null primary key,
    v1 numeric(9,2),
    v2 numeric(9,2),
    v3 numeric(9,2),
    v4 numeric(9,2),
    v5 numeric(9,2),
    v6 numeric(9,2),
    v7 numeric(9,2)
);
insert into sessions values
(1,1,2,3,4,5,6,7),(2,11,12,13,14,15,16,17),(3,21,22,23,24,25,26,27);

create  or replace function do_breakdown(
    v1 numeric(9,2),
    v2 numeric(9,2),
    v3 numeric(9,2),
    v4 numeric(9,2),
    v5 numeric(9,2),
    v6 numeric(9,2),
    v7 numeric(9,2)
) returns breakdown as $$
DECLARE
    D breakdown;
BEGIN
   -- calculate breakdown
   D.f1=v1;
   D.f2=v2;
   D.f3=v3;
   D.f4=v4;
   D.f5=v5;
   D.f6=v6;
   return D;
END;
$$
LANGUAGE PLPGSQL;

create or replace function do_breakdown(vID int4)  RETURNS breakdown
AS $$
DECLARE
   v RECORD;
   D breakdown;
BEGIN
   IF vID IS NULL THEN RETURN NULL; END IF;
   select into v * from sessions s where s.ID = vID;
   IF NOT FOUND THEN
     RAISE NOTICE 'breakdown: % not found',vID;
     RETURN NULL;
   END IF;
   RETURN do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);
END;
$$
LANGUAGE PLPGSQL;