agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@lists.postgresql.org
Subject: Re: select into composite type / return
Date: Thu, 18 Mar 2021 10:05:12 +0000
Message-ID: <7af5d693-a6a3-d3d4-050a-0898aad8bd77@ringways.co.uk> (raw)
In-Reply-To: <9eb42b06-9cfa-1e4b-e42b-6f64e99675a5@ringways.co.uk>
References: <39dcfab9-4eb2-382b-9186-baf722ca56a0@ringways.co.uk>
<3523853.1616002010@sss.pgh.pa.us>
<9eb42b06-9cfa-1e4b-e42b-6f64e99675a5@ringways.co.uk>
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;
view thread (14+ messages) latest in thread
Message-ID: <7af5d693-a6a3-d3d4-050a-0898aad8bd77@ringways.co.uk>
Permalink: ../7af5d693-a6a3-d3d4-050a-0898aad8bd77@ringways.co.uk/
Also on: postgresql.org/message-id/7af5d693-a6a3-d3d4-050a-0898aad8bd77@ringways.co.uk
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk, pgsql-sql@lists.postgresql.org
Subject: Re: select into composite type / return
In-Reply-To: <7af5d693-a6a3-d3d4-050a-0898aad8bd77@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox