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