-- 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)
lmk if this helps you out! [[image /xzilla/templates/default/img/emoticons/smile.png alt=”:-)” style=”display: inline; vertical-align: bottom;” class=”emoticon” /]]
Out Parameter Sql & Plpgsql Examples
I ran into several people on irc this morning who were having trouble with PostgreSQL functions, especially wrt out parameters (an aweful many who were coming from sql server oddly enough). We were all tripping on the syntax, but I eventually got it worked out and said I would post the code here. I’m a little pressed for time so this will be quick any dirty, but I imagine it will still be pretty helpful for those trying to take advantage of the new tech.