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 <fluca1978(at)gmail(dot)com>:

> 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