Re: select into composite type / return

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;