return row from plpgsql?

In response to zhong ming wu :

Hello

I have tried to return rowtypes and record from plpgsql
but they don't look like anything what is returned from select a,b,c
from table d;

Can you show us your function?

I prefer to do this simply as

select aplpgsqlfunction('%u')

The only way it comes close to this postgres documentation is by using
view but it's not possible within my imap server
requirement.

No, you can use a function, no problem. I will show you an example:

test=# select * from foo;
a | b
---+---
1 | 1
2 | 2
3 | 3
(3 rows)

test=# create or replace function f_foo() returns setof record as $$begin return query select * from foo; end; $$language plpgsql;
CREATE FUNCTION
test=# select * from f_foo() as (a int, b int);
a | b
---+---
1 | 1
2 | 2
3 | 3
(3 rows)

As you can see, you have to define the returnig table-structure.
Without the 'as (...)' you got an error:

test=# select * from f_foo();
ERROR: a column definition list is required for functions returning "record"
LINE 1: select * from f_foo();
^

To avoid the eror and the table-definition in your query you can use
IN/OUT-Parameters for your function:

test=# create or replace function f_foo(out x int, out y int) returns setof record as $$begin return query select * from foo; end; $$language plpgsql;
CREATE FUNCTION
test=# select * from f_foo();
x | y
---+---
1 | 1
2 | 2
3 | 3
(3 rows)

HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

On Wed, Mar 17, 2010 at 12:00 AM, Osvaldo Kussama
<osvaldo.kussama@gmail.com> wrote:

For a RETURN SETOF function use:

SELECT * FROM aplpgsqlfunction('%u');

http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

Osvaldo

I didn't know about RETURN SETOF. I will look into it though it seems
just from that example that you have define a separate table just for
that function.