Thread: ...
<p class="MsoNormal"><span lang="EN-ZA">Hi all.</span><p class="MsoNormal"><span lang="EN-ZA">I am new to postgres, so Iam still learning the basics.</span><p class="MsoNormal"><span lang="EN-ZA">In Sequel Server, one can set up a functionto return a table eg:</span><p class="MsoNormal"><span lang="EN-ZA"> </span><p class="MsoNormal"><span lang="EN-ZA">CREATEFUNCTION [dbo].[AuthCodes] (@CLIENTID INT)<span style=""> </span></span><p class="MsoNormal"><span lang="EN-ZA">RETURNS@AuthCodes TABLE</span><p class="MsoNormal"><span lang="EN-ZA">(</span><p class="MsoNormal"><span lang="EN-ZA"><spanstyle=""> </span>[ID] INT,</span><p class="MsoNormal"><span lang="EN-ZA"><span style=""> </span>AUTHCODE VARCHAR(100),</span><p class="MsoNormal"><span lang="EN-ZA"><span style=""> </span>ISSUEDATE DATETIME,</span><p class="MsoNormal"><span lang="EN-ZA"><span style=""> </span>ISSUEDBYVARCHAR(100),</span><p class="MsoNormal"><span lang="EN-ZA"><span style=""> </span>RECIPIENT VARCHAR(100),</span><pclass="MsoNormal"><span lang="EN-ZA"><span style=""> </span>EMAILID VARCHAR(100)<span style=""> </span></span><p class="MsoNormal"><span lang="EN-ZA">)</span><p class="MsoNormal"><span lang="EN-ZA"> </span><pclass="MsoNormal"><span lang="EN-ZA">and then use select into or insert to populate the table.</span><pclass="MsoNormal"><span lang="EN-ZA">I am really battling to figure out how to do the same in a postgres function.</span><pclass="MsoNormal"><span lang="EN-ZA">It seems like one would have to create a user defined type (myTypefor 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 amguessing that I am on the wrong track…does anyone have any suggestions, or examples I could follow?</span>
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
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 ===
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
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