Klay Martens wrote:
Hi all.
I am new to postgres, so I am still learning the basics.
In Sequel Server, one can set up a function to return a table eg:
CREATE FUNCTION [dbo].[AuthCodes] (@CLIENTID INT)
RETURNS @AuthCodes TABLE
[snip]
and then use select into or insert to populate the table.
I am really battling to figure out how to do the same in a postgres function.
It seems like one would have to create a user defined type (myType for eg), and
then set the return type of the function to be a set of myType, but I can’t seem
to make it work. I am guessing that I am on the wrong track…does anyone have any
suggestions, or examples I could follow?
You've got the right idea. If there's not an existing table, define a
type, return SETOF my_type and use RETURN NEXT inside the function to
return each row. You call the function as: "SELECT * FROM myfunc()",
that is - treat it as a table.
There's an item on set-returning functions here:
http://techdocs.postgresql.org/
and here
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
