agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: select into composite type / return
Date: Wed, 17 Mar 2021 13:26:50 -0400
Message-ID: <3523853.1616002010@sss.pgh.pa.us> (raw)
In-Reply-To: <39dcfab9-4eb2-382b-9186-baf722ca56a0@ringways.co.uk>
References: <39dcfab9-4eb2-382b-9186-baf722ca56a0@ringways.co.uk>

Gary Stainburn <gary.stainburn@ringways.co.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

view thread (14+ messages)  latest in thread

Message-ID: <3523853.1616002010@sss.pgh.pa.us>
Permalink:  ../3523853.1616002010@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3523853.1616002010@sss.pgh.pa.us

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: tgl@sss.pgh.pa.us, gary.stainburn@ringways.co.uk, pgsql-sql@lists.postgresql.org
  Subject: Re: select into composite type / return
  In-Reply-To: <3523853.1616002010@sss.pgh.pa.us>

* 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