pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

help defining a stored procedure that returns a record or an array using SELECT INTO
2+ messages / 2 participants
[nested] [flat]

* help defining a stored procedure that returns a record or an array using SELECT INTO
@ 2018-02-02 09:21  Luca Ferrari <fluca1978@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Luca Ferrari @ 2018-02-02 09:21 UTC (permalink / raw)
  To: 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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: help defining a stored procedure that returns a record or an array using SELECT INTO
@ 2018-02-02 09:42  Pavel Stehule <pavel.stehule@gmail.com>
  parent: Luca Ferrari <fluca1978@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Pavel Stehule @ 2018-02-02 09:42 UTC (permalink / raw)
  To: Luca Ferrari <fluca1978@gmail.com>; +Cc: pgsql-sql

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


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2018-02-02 09:42 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-02-02 09:21 help defining a stored procedure that returns a record or an array using SELECT INTO Luca Ferrari <fluca1978@gmail.com>
2018-02-02 09:42 ` Pavel Stehule <pavel.stehule@gmail.com>

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