pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed(no subject)
5+ messages / 5 participants
[nested] [flat]
* (no subject)
@ 2006-03-09 13:35 Klay Martens <kmartens@wol.co.za>
2006-03-09 13:49 ` Re: Leif B. Kristensen <leif@solumslekt.org>
2006-03-09 13:53 ` Re: A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-03-09 13:59 ` Re: Richard Huxton <dev@archonet.com>
2006-03-10 12:40 ` Re: Markus Schaber <schabi@logix-tt.com>
0 siblings, 4 replies; 5+ messages in thread
From: Klay Martens @ 2006-03-09 13:35 UTC (permalink / raw)
To: pgsql-sql
<p class="MsoNormal"><span lang="EN-ZA">Hi all.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA">I am new to postgres, so I am still
learning the basics.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA">In Sequel Server, one can set up a function
to return a table eg:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA">CREATE FUNCTION [dbo].[AuthCodes]
(@CLIENTID INT)<span style=""> </span><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA">RETURNS @AuthCodes TABLE<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA">(<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA"><span style=""> </span>[ID]
INT,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA"><span style=""> </span>AUTHCODE
VARCHAR(100),<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA"><span style=""> </span>ISSUEDATE
DATETIME,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA"><span style=""> </span>ISSUEDBY
VARCHAR(100),<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA"><span style=""> </span>RECIPIENT
VARCHAR(100),<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA"><span style=""> </span>EMAILID
VARCHAR(100)<span style=""> </span><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA">)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA">and then use select into or insert to
populate the table.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA">I am really battling to figure out how to
do the same in a postgres function.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-ZA">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?<o:p></o:p></span></p>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re:
2006-03-09 13:35 (no subject) Klay Martens <kmartens@wol.co.za>
@ 2006-03-09 13:49 ` Leif B. Kristensen <leif@solumslekt.org>
3 siblings, 0 replies; 5+ messages in thread
From: Leif B. Kristensen @ 2006-03-09 13:49 UTC (permalink / raw)
To: pgsql-sql
On Thursday 09 March 2006 14:35, Klay Martens wrote:
><p class="MsoNormal"><span lang="EN-ZA">Hi all.<o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA">I am new to postgres, so I am
> still learning the basics.<o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA">In Sequel Server, one can set
> up a function to return a table eg:<o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA"><o:p> </o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA">CREATE FUNCTION
> [dbo].[AuthCodes] (@CLIENTID INT)<span style="">
> </span><o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA">RETURNS @AuthCodes
> TABLE<o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA">(<o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA"><span
> style="">
> </span>[ID] INT,<o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA"><span
> style="">
> </span>AUTHCODE VARCHAR(100),<o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA"><span
> style="">
> </span>ISSUEDATE DATETIME,<o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA"><span
> style="">
> </span>ISSUEDBY VARCHAR(100),<o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA"><span
> style="">
> </span>RECIPIENT VARCHAR(100),<o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA"><span
> style="">
> </span>EMAILID VARCHAR(100)<span style="">
> </span><o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA">)<o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA"><o:p> </o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA">and then use select into or
> insert to populate the table.<o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA">I am really battling to figure
> out how to do the same in a postgres function.<o:p></o:p></span></p>
>
>
><p class="MsoNormal"><span lang="EN-ZA">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?<o:p></o:p></span></p>
Please don't post HTML messages to mail lists.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re:
2006-03-09 13:35 (no subject) Klay Martens <kmartens@wol.co.za>
@ 2006-03-09 13:53 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
3 siblings, 0 replies; 5+ messages in thread
From: A. Kretschmer @ 2006-03-09 13:53 UTC (permalink / raw)
To: pgsql-sql
am 09.03.2006, um 15:35:45 +0200 mailte Klay Martens folgendes:
>
>
> <p class="MsoNormal"><span lang="EN-ZA">Hi all.<o:p></o:p></span></p>
>
>
> <p class="MsoNormal"><span lang="EN-ZA">I am new to postgres, so I am still
> learning the basics.<o:p></o:p></span></p>
Please, write in plain ASCII.
Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re:
2006-03-09 13:35 (no subject) Klay Martens <kmartens@wol.co.za>
@ 2006-03-09 13:59 ` Richard Huxton <dev@archonet.com>
3 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2006-03-09 13:59 UTC (permalink / raw)
To: Klay Martens <kmartens@wol.co.za>; +Cc: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re:
2006-03-09 13:35 (no subject) Klay Martens <kmartens@wol.co.za>
@ 2006-03-10 12:40 ` Markus Schaber <schabi@logix-tt.com>
3 siblings, 0 replies; 5+ messages in thread
From: Markus Schaber @ 2006-03-10 12:40 UTC (permalink / raw)
To: Klay Martens <kmartens@wol.co.za>; +Cc: pgsql-sql
Hi, Klay,
Klay Martens wrote:
> I am really battling to figure out how to do the same in a postgres
> function.
http://www.postgresql.org/docs/8.1/static/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS
could be helpful.
HTH
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-03-10 12:40 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-09 13:35 Klay Martens <kmartens@wol.co.za>
2006-03-09 13:49 ` Re: Leif B. Kristensen <leif@solumslekt.org>
2006-03-09 13:53 ` Re: A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-03-09 13:59 ` Re: Richard Huxton <dev@archonet.com>
2006-03-10 12:40 ` Re: Markus Schaber <schabi@logix-tt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox