Record with a field consisting of table rows

From: Jon Smark <jon(dot)smark(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Record with a field consisting of table rows
Date: 2011-01-13 16:22:16
Message-ID: 611127.80007.qm@web112816.mail.gq1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christian Walter 2011-01-13 16:31:24 Re: Optimal settings for embedded system running PostgreSQL
Previous Message Andrew Sullivan 2011-01-13 16:18:02 Re: Case Sensitivity