agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: select into composite type / return
Date: Wed, 17 Mar 2021 16:48:45 +0000
Message-ID: <39dcfab9-4eb2-382b-9186-baf722ca56a0@ringways.co.uk> (raw)

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

view thread (14+ messages)  latest in thread

Message-ID: <39dcfab9-4eb2-382b-9186-baf722ca56a0@ringways.co.uk>
Permalink:  ../39dcfab9-4eb2-382b-9186-baf722ca56a0@ringways.co.uk/
Also on:    postgresql.org/message-id/39dcfab9-4eb2-382b-9186-baf722ca56a0@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: <39dcfab9-4eb2-382b-9186-baf722ca56a0@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