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

From: Luca Ferrari <fluca1978(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: help defining a stored procedure that returns a record or an array using SELECT INTO
Date: 2018-02-02 09:21:03
Message-ID: CAKoxK+4DHd-axJUW3tyeXuRdoBO=xLE83ajaCCeR5uqDuoWshw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Pavel Stehule 2018-02-02 09:42:06 Re: help defining a stored procedure that returns a record or an array using SELECT INTO
Previous Message Olivier Leprêtre 2018-02-02 09:00:28 search inside partitions