Re: [Fwd: Returning multiple rows in 8.4]

On 9 Nov 2009, at 23:30, Baris Gerze wrote:
> I have users table such as
> create table users(
> user_id bigserial,
> user_name varchar(50),
> user_surname varchar(100)
> );
>
>
> how can I write a function to return multiple rows? ( in plpgsql )

You can create set-returning functions (http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET
).

> what is the return value of the function? records?

That's up to you, but most likely you want to return a type that you
defined somewhere, that being a table you created or a custom type.

You probably want "returns setof users". You can also use "returns
setof record", but then you'll have to specify what that record
consists of in every query that uses your function.

> and how can i run it without running as select my_func() as (int8,
> varchar) ???
> is there simple way to do this?

Set returning functions are queried as select * from my_func();
If you return a set of records then you'll have to specify it's type
the way you mention above.

> thanks

You're welcome.

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.

!DSPAM:737,4af9464211073046225299!