From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
Cc: | "pgsql-sql(at)lists(dot)postgresql(dot)org" <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | Re: select into composite type / return |
Date: | 2021-03-17 17:26:50 |
Message-ID: | 3523853.1616002010@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> writes:
> 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.
AFAICS these two functions will have exactly the same output behavior,
ie returning a "breakdown" composite type. If they act differently
for you, either you are calling them in different ways or you made
a mistake somewhere. I can't help noticing that the RETURN in the
second function is calling a six-argument function, which is not the
one you showed first. Maybe that version of do_breakdown() returns
something different?
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Stainburn | 2021-03-17 17:42:34 | Re: select into composite type / return |
Previous Message | Gary Stainburn | 2021-03-17 16:48:45 | select into composite type / return |