Re: help defining a stored procedure that returns a record or an array using SELECT INTO

Hi

2018-02-02 10:21 GMT+01:00 Luca Ferrari <[email protected]>:

> Hi all,
> this may sound trivial, but I'm not able to create a simple function
> that returns either an array or a record.
> For instance, the following:
>
> CREATE TYPE t_agg AS (
>   t_count   int,
>   t_sum int
> );
>
> CREATE OR REPLACE FUNCTION f_compute()
> RETURNS t_agg
> AS
> $BODY$
>   DECLARE
>     tot t_agg%rowtype;
>   BEGIN
>     SELECT  count(id)
>             , sum( f_value )
>     INTO STRICT tot
>     FROM my_table;
>
>     RETURN tot;
>
>   END
> $BODY$
> LANGUAGE plpgsql;
>
>
> provides a tuple of t_agg with a simple count and sum.
>
> 1) is it possible to change the return value to int[] (and
> consequently tot variable) using the SELECT INTO statement?
>

yes

postgres=# create or replace function fx()
returns pt as $$
declare result pt;
begin
  select 1,2 into result;
  return result;
end;
$$ language plpgsql;

postgres=# select fx();
┌───────┐
│  fx   │
╞═══════╡
│ (1,2) │
└───────┘
(1 row)

postgres=# select * from fx();
┌───┬───┐
│ x │ y │
╞═══╪═══╡
│ 1 │ 2 │
└───┴───┘
(1 row)

create or replace function fx1()
returns pt[] as $$
declare result pt[];
begin
  select array_agg(row(1,2)) from generate_series(1,5) into result;
  return result;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select * from fx1();
┌───────────────────────────────────────────┐
│                    fx1                    │
╞═══════════════════════════════════════════╡
│ {"(1,2)","(1,2)","(1,2)","(1,2)","(1,2)"} │
└───────────────────────────────────────────┘
(1 row)



> 2) if I change the return type to record (and consequently the tot
> variable), how should I name columns so that the parse knows what
> tuple it is returning still using the SELECT INTO?
>

When you use RECORD as result type, then you have to use tuple descriptor
when you are call function

create or replace function fx2()
returns record as $$
declare result pt;
begin
  select 1,2 into result;
  return result;
end;
$$ language plpgsql;

-- unnamed fields
postgres=# select fx2();
┌───────┐
│  fx2  │
╞═══════╡
│ (1,2) │
└───────┘
(1 row)

postgres=# select * from fx2() as (x int, y int);
┌───┬───┐
│ x │ y │
╞═══╪═══╡
│ 1 │ 2 │
└───┴───┘
(1 row)



>
> Thanks,
> Luca
>
>
Regards

Pavel