Обсуждение: select into composite type / return

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

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

On 17/03/2021 17:26, Tom Lane wrote:
> 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
Hi Tom,

Thanks for the quick response.  The missing argument was because I had 
to use pseudo details to protect intellectual property and I miss-typed 
the code.

You were correct.  The problem was not with the functions, but with how 
I was calling them.

Gary

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;

Hi Gary,

a shot in the dark but maybe

SELECT id, (do_breakdown(id)).* 

FROM   ...

already does the job?  (I'm on 13.2 here and my quickly whipped up example shows the desired behavior.)

Best wishes,  

   —Torsten

Torsten Grust <torsten.grust@gmail.com> writes:
> Hi Gary,
> a shot in the dark but maybe

> SELECT id, (do_breakdown(id)).*
> FROM   ...

> already does the job?

Beware --- what that actually does is expand into

SELECT id, (do_breakdown(id)).f1, (do_breakdown(id)).f2, ...

so that the function will be invoked N times if it produces N columns.

What you generally want to do is invoke the function as a lateral FROM
item:

SELECT id, f.* FROM table AS t, LATERAL do_breakdown(t.id) AS f;

            regards, tom lane

On 18/03/2021 14:28, Tom Lane wrote:
> Beware --- what that actually does is expand into
> SELECT id, (do_breakdown(id)).f1, (do_breakdown(id)).f2, ...
>
> so that the function will be invoked N times if it produces N columns.
>
> What you generally want to do is invoke the function as a lateral FROM
> item:
>
> SELECT id, f.* FROM table AS t, LATERAL do_breakdown(t.id) AS f;
>
>             regards, tom lane
Thanks for the info Tom, I can see how that would be quite a performance 
hit, not to mention adverse effects if these functions start doing updates.

gary=# SELECT id, f.* FROM sessions AS t, LATERAL do_breakdown(t.id) AS f;
  id |  f1   |  f2   |  f3   |  f4   |  f5   |  f6
----+-------+-------+-------+-------+-------+-------
   1 |  1.00 |  2.00 |  3.00 |  4.00 |  5.00 |  6.00
   2 | 11.00 | 12.00 | 13.00 | 14.00 | 15.00 | 16.00
   3 | 21.00 | 22.00 | 23.00 | 24.00 | 25.00 | 26.00
(3 rows)

gary=#

I've added another function, partly to aid debugging, partly to test the 
next part of the project.

The idea is simple.  select the results of the calculation into a local 
variable and then process it.  However, I can't get the select to work.  
The failure message relates to the "select into D" line.

gary=# select * from read_breakdown(1);
ERROR:  invalid input syntax for type numeric: 
"(1.00,2.00,3.00,4.00,5.00,6.00)"
CONTEXT:  PL/pgSQL function read_breakdown(integer) line 12 at SQL statement
gary=#


create or replace function read_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;
   select into D do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);
   IF NOT FOUND THEN
     RAISE NOTICE 'breakdown: % calculation failed',vID;
     RETURN NULL;
   END IF;
   RAISE NOTICE 'read_breakdown: f1=%',D.f1;
   RAISE NOTICE 'read_breakdown: f2=%',D.f2;
   RAISE NOTICE 'read_breakdown: f3=%',D.f3;
   RAISE NOTICE 'read_breakdown: f4=%',D.f4;
   RAISE NOTICE 'read_breakdown: f5=%',D.f5;
   RAISE NOTICE 'read_breakdown: f6=%',D.f6;
   RETURN D;
END;
$$
LANGUAGE PLPGSQL;

select into D do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);

this line is wrong

you should to use select * from do_breakdown(..) into D

note - plpgsql is case insensitive language - all identifiers are transformed to lower case form - so using identifiers like D is not a good idea.

Regards

Pavel

probably you can write

D := do_breakdown(...);

and it will be faster

Pavel