Re: select into composite type / return

Hi Gary,

a shot in the dark but maybe

SELECT id, (do_breakdown(id)).*
FROM ...

already does the job? I'm on 13.2 here and my quickly whipped up example
shows the desired behavior:

# SELECT (f(1)).*;
a | b | c
---+---+---
1 | 2 | 3

Best wishes,
—Torsten

On Thu, Mar 18, 2021 at 11:05 AM Gary Stainburn <
gary(dot)stainburn(at)ringways(dot)co(dot)uk> wrote:

> 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;
>
>
>
>
>

--
| Torsten Grust
| Torsten(dot)Grust(at)gmail(dot)com