Laramie/postgresql/CreatingFunctions

From ThinkPrank

Found this doco on http://people.planetpostgresql.org/xzilla/index.php?/archives/149-guid.html

-- make some sample data to play with
create table users (user_id integer, username text, created timestamptz);
insert into users values (1,'one',now());
insert into users values (2,'two',now());
insert into users values (3,'three',now());

-- here is the basic sql based setof record function
create or replace function myuser(in myuser integer, out my_user_id integer, out myusername text)
returns setof record as $$ select user_id, username from users where user_id <= $1; $$ language 'sql';

dev20100=# select myuser(2);
myuser
---------
(1,one)
(2,two)
(2 rows)

dev20100=# select * from myuser(2);
my_user_id | myusername
------------+------------
1 | one
2 | two
(2 rows)


-- Now we do the same thing with plpgsql, this is trickier than it looks when reading the docs!

create or replace function myuser2(myuser integer, out my_user_id integer, out myusername text) returns setof record as $$
declare
foo record;
begin
for foo in select user_id, username from users where user_id <= $1 loop
my_user_id=foo.user_id;
myusername=foo.username;
return next;
end loop;
return;
end $$ language 'plpgsql';

dev20100=# select * from myuser(2);
my_user_id | myusername
------------+------------
1 | one
2 | two
(2 rows)

-- and just for kicks someone wanted to see the non-setof just record way in plpsql

create or replace function myuser3(myuser integer, out my_user_id integer, out myusername text)
returns record as $$
begin
select user_id, username from users where user_id <= $1 into my_user_id, myusername;
return;
end $$ language 'plpgsql';

dev20100=# select * from myuser3(2);
my_user_id | myusername
------------+------------
1 | one
(1 row)