From: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
---|---|
To: | "pgsql-sql(at)lists(dot)postgresql(dot)org" <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | select into composite type / return |
Date: | 2021-03-17 16:48:45 |
Message-ID: | 39dcfab9-4eb2-382b-9186-baf722ca56a0@ringways.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I have a function that takes 7 numerical inputs, performs calculations,
and then returns a composite type.
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 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
return D;
END;
$$
LANGUAGE PLPGSQL;
This works great, returning one row with the separate columns.
I now want to set up another function which will take a key, retrieve
the arguments from a table, and call the first function. The problem is
that I can't get the syntax correct to return the composite type. I
have tried
create function do_breakdown(key text) returns breakdown as $$
DECLARE
v RECORD;
BEGIN
select into v * from stored s where key s.key = key;
RETURN do_breakdown(v.f1,v.f2,v.f3,v.f4,v.f5,v.f6);
END;
$$
LANGUAGE PLPGSQL;
but it returns the whole thing as a single column. Adding a typecase
didn't help.
I have also tried
create function do_breakdown(key text) returns breakdown as $$
DECLARE
v RECORD;
D breakdown;
BEGIN
select into v * from stored s where key s.key = key;
select into D * from do_breakdown(v.f1,v.f2,v.f3,v.f4,v.f5,v.f6);
RETURN D;
END;
$$
LANGUAGE PLPGSQL;
but that also returns everything as a single column. Any help would be
appreciated.
Gary
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-03-17 17:26:50 | Re: select into composite type / return |
Previous Message | Tom Lane | 2021-02-20 15:58:21 | Re: embedded composite types |