select into composite type / return

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

Responses

Browse pgsql-sql by date

  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