From: Pavel Stehule <pavel.stehule@gmail.com>
To: Luca Ferrari <fluca1978@gmail.com>
Cc: postgres list <pgsql-sql@postgresql.org>
Subject: Re: help defining a stored procedure that returns a record or an array using SELECT INTO
Date: Fri, 2 Feb 2018 10:42:06 +0100
Message-ID: <CAFj8pRAsq6HPM2nb5X9cbtEg--xtwJvYNTOSvoh+MHCsrmWLsg@mail.gmail.com> (raw)
In-Reply-To: <CAKoxK+4DHd-axJUW3tyeXuRdoBO=xLE83ajaCCeR5uqDuoWshw@mail.gmail.com>
References: <CAKoxK+4DHd-axJUW3tyeXuRdoBO=xLE83ajaCCeR5uqDuoWshw@mail.gmail.com>
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)
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pavel.stehule@gmail.com, fluca1978@gmail.com
Subject: Re: help defining a stored procedure that returns a record or an array using SELECT INTO
In-Reply-To: <CAFj8pRAsq6HPM2nb5X9cbtEg--xtwJvYNTOSvoh+MHCsrmWLsg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox