Thread: help defining a stored procedure that returns a record or an arrayusing SELECT INTO
help defining a stored procedure that returns a record or an arrayusing SELECT INTO
From
Luca Ferrari
Date:
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? 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? Thanks, Luca
Re: help defining a stored procedure that returns a record or anarray using SELECT INTO
From
Pavel Stehule
Date:
Hi
2018-02-02 10:21 GMT+01:00 Luca Ferrari <fluca1978@gmail.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)
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;
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)
┌───────┐
│ fx2 │
╞═══════╡
│ (1,2) │
└───────┘
(1 row)
postgres=# select * from fx2() as (x int, y int);
┌───┬───┐
│ x │ y │
╞═══╪═══╡
│ 1 │ 2 │
└───┴───┘
(1 row)
Thanks,
Luca
Regards
Pavel