Re: Record with a field consisting of table rows

On 13 Jan 2011, at 17:22, Jon Smark wrote:

> 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
> $$;


I think it would be easier to rewrite that to a set-returning function returning TABLE (...).

Something like this (untested):

create function get_page ()
returns setof table (total int, user users)
language plpgsql as
$$
declare
    _total   int;
begin
    _total := select count (*) from users;

    return query select _total AS total, u from users AS u limit 10;
end
$$;

In general it is considered a bad idea to rely on what * returns though, it's better to return the columns explicitly.

Which makes me wonder, is table cloning supported for these cases? For example:

create function get_page ()
returns setof table (LIKE users, total int)
...



Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4d2f50bf11877227918328!