Subject: Re: select into composite type / return
To: pgsql-sql@lists.postgresql.org
References: <39dcfab9-4eb2-382b-9186-baf722ca56a0@ringways.co.uk>
 <3523853.1616002010@sss.pgh.pa.us>
 <9eb42b06-9cfa-1e4b-e42b-6f64e99675a5@ringways.co.uk>
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Message-ID: <7af5d693-a6a3-d3d4-050a-0898aad8bd77@ringways.co.uk>
Date: Thu, 18 Mar 2021 10:05:12 +0000
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <9eb42b06-9cfa-1e4b-e42b-6f64e99675a5@ringways.co.uk>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
 
 Content preview:  I now have the working functions. The first accepts 7 arguments
    and returns� a composite type of the calculations breakdown. The second takes
    a single argument and retrieves the 7 arguments from a table before calling
    the first argument. [...] 
 
 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.6 AWL                    AWL: Adjusted score from AWL reputation of From: address
  1.0 MISSING_FROM           Missing From: header
 -0.0 NICE_REPLY_A           Looks like a legit reply (A)
  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/7af5d693-a6a3-d3d4-050a-0898aad8bd77%40ringways.co.uk>
Precedence: bulk

I now have the working functions.

The first accepts 7 arguments and returns  a composite type of the 
calculations breakdown.
The second takes a single argument and retrieves the 7 arguments from a 
table before calling the first argument.

What I can't get my head round is how I can use these functions to 
return a setof breakdowns. All I can get is thebreakdown returned as a 
single column.

All advice welcome.

users=# select * from do_breakdown(1);
   f1  |  f2  |  f3  |  f4  |  f5  |  f6
------+------+------+------+------+------
  1.00 | 2.00 | 3.00 | 4.00 | 5.00 | 6.00
(1 row)

users=# select * from sessions;
  id |  v1   |  v2   |  v3   |  v4   |  v5   |  v6   |  v7
----+-------+-------+-------+-------+-------+-------+-------
   1 |  1.00 |  2.00 |  3.00 |  4.00 |  5.00 |  6.00 |  7.00
   2 | 11.00 | 12.00 | 13.00 | 14.00 | 15.00 | 16.00 | 17.00
   3 | 21.00 | 22.00 | 23.00 | 24.00 | 25.00 | 26.00 | 27.00
(3 rows)

users=# select id, do_breakdown(id) from sessions where id in (1,3);
  id |             do_breakdown
----+---------------------------------------
   1 | (1.00,2.00,3.00,4.00,5.00,6.00)
   3 | (21.00,22.00,23.00,24.00,25.00,26.00)
(2 rows)

users=#


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 table sessions (
    ID int4 not null primary key,
    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)
);
insert into sessions values 
(1,1,2,3,4,5,6,7),(2,11,12,13,14,15,16,17),(3,21,22,23,24,25,26,27);

create  or replace 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
   D.f1=v1;
   D.f2=v2;
   D.f3=v3;
   D.f4=v4;
   D.f5=v5;
   D.f6=v6;
   return D;
END;
$$
LANGUAGE PLPGSQL;

create or replace function do_breakdown(vID int4)  RETURNS breakdown
AS $$
DECLARE
   v RECORD;
   D breakdown;
BEGIN
   IF vID IS NULL THEN RETURN NULL; END IF;
   select into v * from sessions s where s.ID = vID;
   IF NOT FOUND THEN
     RAISE NOTICE 'breakdown: % not found',vID;
     RETURN NULL;
   END IF;
   RETURN do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);
END;
$$
LANGUAGE PLPGSQL;