To: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Subject: select into composite type / return
Message-ID: <39dcfab9-4eb2-382b-9186-baf722ca56a0@ringways.co.uk>
Date: Wed, 17 Mar 2021 16:48:45 +0000
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
 
 Content preview:  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) ); [...] 
 
 Content analysis details:   (-48.4 points, 15.0 required)
 
  pts rule name              description
 ---- ---------------------- --------------------------------------------------
  -50 ALL_TRUSTED            Passed through trusted hosts only via SMTP
 -1.9 BAYES_00               BODY: Bayes spam probability is 0 to 1%
                             [score: 0.0000]
  2.0 FLOWED                 Conten Type format=flowed appears in the new SPAMS
  0.1 SCORE_RCPTS            Adding score for each recipient
  0.1 SCORE_RCPTS            Adding score for each recipient
 -0.7 AWL                    AWL: Adjusted score from AWL reputation of From: address
  1.0 MISSING_FROM           Missing From: header
  1.0 RING_SAFE              No description available.
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Archived-At: 
 <https://www.postgresql.org/message-id/39dcfab9-4eb2-382b-9186-baf722ca56a0%40ringways.co.uk>
Precedence: bulk

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