Quick Links

Re:

Lists:	pgsql-sql

From:	"Klay Martens" <kmartens(at)wol(dot)co(dot)za>
To:	pgsql-sql(at)postgresql(dot)org
Subject:
Date:	2006-03-09 13:35:45
Message-ID:	b62baae3da9741128a55d5039b7845ea@wol.co.za
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-sql

Hi all.<o:p></o:p>

I am new to postgres, so I am still
learning the basics.<o:p></o:p>

In Sequel Server, one can set up a function
to return a table eg:<o:p></o:p>

<o:p> </o:p>

CREATE FUNCTION [dbo].[AuthCodes]
(@CLIENTID INT)  <o:p></o:p>

RETURNS @AuthCodes TABLE<o:p></o:p>

(<o:p></o:p>

[ID]
INT,<o:p></o:p>

AUTHCODE
VARCHAR(100),<o:p></o:p>

ISSUEDATE
DATETIME,<o:p></o:p>

ISSUEDBY
VARCHAR(100),<o:p></o:p>

RECIPIENT
VARCHAR(100),<o:p></o:p>

EMAILID
VARCHAR(100)    <o:p></o:p>

)<o:p></o:p>

<o:p> </o:p>

and then use select into or insert to
populate the table.<o:p></o:p>

I am really battling to figure out how to
do the same in a postgres function.<o:p></o:p>

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>

Attachment	Content-Type	Size
unknown_filename	text/html	2.5 KB

From:	"Leif B(dot) Kristensen" <leif(at)solumslekt(dot)org>
To:	pgsql-sql(at)postgresql(dot)org
Subject:	Re:
Date:	2006-03-09 13:49:34
Message-ID:	200603091449.35028.leif@solumslekt.org
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-sql

On Thursday 09 March 2006 14:35, Klay Martens wrote:
>Hi all.<o:p></o:p>
>
>
>I am new to postgres, so I am
> still learning the basics.<o:p></o:p>
>
>
>In Sequel Server, one can set
> up a function to return a table eg:<o:p></o:p>
>
>
><o:p> </o:p>
>
>
>CREATE FUNCTION
> [dbo].[AuthCodes] (@CLIENTID INT) 
> <o:p></o:p>
>
>
>RETURNS @AuthCodes
> TABLE<o:p></o:p>
>
>
>(<o:p></o:p>
>
>
> style="">          
>  [ID] INT,<o:p></o:p>
>
>
> style="">          
>  AUTHCODE VARCHAR(100),<o:p></o:p>
>
>
> style="">          
>  ISSUEDATE DATETIME,<o:p></o:p>
>
>
> style="">          
>  ISSUEDBY VARCHAR(100),<o:p></o:p>
>
>
> style="">          
>  RECIPIENT VARCHAR(100),<o:p></o:p>
>
>
> style="">          
>  EMAILID VARCHAR(100)   
> <o:p></o:p>
>
>
>)<o:p></o:p>
>
>
><o:p> </o:p>
>
>
>and then use select into or
> insert to populate the table.<o:p></o:p>
>
>
>I am really battling to figure
> out how to do the same in a postgres function.<o:p></o:p>
>
>
>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>

Please don't post HTML messages to mail lists.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

From:	"A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To:	pgsql-sql(at)postgresql(dot)org
Subject:	Re:
Date:	2006-03-09 13:53:12
Message-ID:	20060309135312.GB15481@webserv.wug-glas.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-sql

am 09.03.2006, um 15:35:45 +0200 mailte Klay Martens folgendes:
>
>
> Hi all.<o:p></o:p>
>
>
> I am new to postgres, so I am still
> learning the basics.<o:p></o: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 ===

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
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:	Markus Schaber <schabi(at)logix-tt(dot)com>
To:	Klay Martens <kmartens(at)wol(dot)co(dot)za>
Cc:	pgsql-sql(at)postgresql(dot)org
Subject:	Re:
Date:	2006-03-10 12:40:45
Message-ID:	441173CD.7070905@logix-tt.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	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