Re: Record with a field consisting of table rows

Hello

try to SELECT INTO ARRAY(SELECT user_t FROM users LIMIT 10) _page.users;

Regards

Pavel Stehule

2011/1/13 Jon Smark <jon(dot)smark(at)yahoo(dot)com>:
> Hi,
>
> I am trying to create a PL/pgSQL function whose return type is a tuple
> consisting of an integer and a list of table rows.  I emulate the tuple
> by defining a record 'page_t' with the two fields; however, the naïve
> approach of doing a SELECT INTO one the record's fields does not work
> (see function 'get_page') below.  Am I missing something obvious here?
>
> Thanks in advance!
> Jon
>
>
> create table users
>        (
>        uid     int4 not null,
>        name    text not null,
>        age     int4 not null,
>        primary key (uid)
>        );
>
> create type user_t AS
>        (
>        uid     int4,
>        name    text,
>        age     int4
>        );
>
> create type page_t AS
>        (
>        total   int4,
>        users   user_t[]
>        );
>
> create function get_page ()
> returns page_t
> language plpgsql as
> $$
> declare
>        _page   page_t;
> begin
>        _page.total := select count (*) from users;
>        select * into _page.users from users limit 10;
>        return _page;
> end
> $$;
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>