Re: 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