question about returns "set"

[thedumbkid]

Is returns "set" the same as returning a table???
by that I mean, if I have a function that returns "set"
can I do the following?

create function myfunction() returns "set" as '
begin
return [some SELECT statement here];
end;' language 'plpgsql';

if so, can somebody point me to some documentation on how to use this return type? I can't find a good resource for some reason

I know there's "returns setof record" and "returns refcursor"... but I'm hoping that returns "set" can make my life easier

reason I wanna do this is because I have many functions in SQL Server and a lot of them have RETURN TABLE...

 

[rycamor]

See

http://techdocs.postgresql.org/guides/SetReturningFunctionsset-returning

in the documentation. Also see the following links, copied from a recent PostgreSQL mailing list message:

http://techdocs.postgresql.org/guides/SetReturningFunctions

http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=11378

There are plpgsql and sql function examples in the mailing list archives. Couple of examples:

http://archives.postgresql.org/pgsql-general/2002-10/msg00022.php

and

http://archives.postgresql.org/pgsql-general/2002-12/msg00426.php

There is a recent example in Elein Mustain's "General Bits" column:

http://archives.postgresql.org/pgsql-general/2002-12/msg00426.php

And finally, in the manual see:
general ->

http://developer.postgresql.org/docs/postgres/xfunc-tablefunctions.html

sql ->

http://developer.postgresql.org/docs/postgres/xfunc-sql.html

(sec 9.2.4)
C ->

http://developer.postgresql.org/docs/postgres/xfunc-c.html

(sec 9.5.6)
plpgsql ->

http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html

usage ->

http://developer.postgresql.org/docs/postgres/sql-select.html

(from_item, inputs, from clause, usage)

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[thedumbkid]

hi rycamor, thank you for the links

I am converting everything from SQL Server to PostgreSQL for an application.
It's going to support Oracle, SQL Server and PostgreSQL so I need a solution
that's easy to maintain.

Is refcursor the only way to get a set of rows if I don't want to use
"returns setof record" and "returns setof my_own_type"?

Are there any other ways of returning a collection of rows??? Like in SQL Server
where you can just do RETURN [SELECT STATEMENT].

I have functions returning a SELECT query that gets columns from several
views/tables... using inner join and all that stuff...
Some of them return like 30 columns... imagine having to specify all those columns
in "select * from myfunction() as (...)" or CREATE TYPE to use for all those
functions...

"return record" and "return my_own_type" are hard to maintain in my case... and if
possible, I don't want to use those

just wondering if there's a good way of doing this

 

[rycamor]

I imagine there should be some way to get a generic return, even if you need a small standardized piece of code at the beginning of each function. You should ask this question in the PostgreSQL mailing list (see

http://archives.postgresql.org

to sign up). Try the pgsql-sql@postgresql.org list.

I'm pretty sure you will get a fairly workable solution. Often the head developers themselves will answer your questions ;-).

I'm curious about the answer to this also, so please let us know what you find out.

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[thedumbkid]

got an answer from mailing list that this is not possible in postgresql yet

I am using user-defined types now