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!