From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Klay Martens <kmartens(at)wol(dot)co(dot)za> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: |
Date: | 2006-03-09 13:59:16 |
Message-ID: | 441034B4.1060400@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Flávio Suguimoto | 2006-03-09 14:04:44 | Re: Problems with disabling triggers in Postgres 7.3.9 |
Previous Message | Alvaro Herrera | 2006-03-09 13:55:57 | Re: Problems with disabling triggers in Postgres 7.3.9 |