Обсуждение: return row from plpgsql?

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;

My application is for dovecot imap server userdb where I can only put
one sql statement
to retrieve a few items.  I need to perform some logic in retrieving
and my work-around currently is something like
   select functiona(a.b) as home,functionb(b.c) as mail from sometable
a, sometable b where a.id=b.id and a.email='%u'

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.

Thanks for any suggestion or at least confirmation that it's not
possible with plpgsql

mr.wu

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;
$$languageplpgsql; 
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 *
fromfoo; 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.