> I installed 7.3beta3 but how do I do it now? > How do I make a function that returns a dataset > > Greets > Zertox
He is an email I received from Joe Conway on the subject a month or so ago > Adam Witney wrote: >> There have been a few emails recently concerning using functions. However I >> am a little confused as to their use with composite types. I can see how to >> return a whole row from a table, but is it possible to return multiple >> fields that do not originate from the same table? > > Sure. But you either need a named composite type that matches the row you want > to return, or you can use a record datatype and specify the column definitions > in the sql statement at run time. > > A composite type exists for each table and view in your database, as well as > any stand-alone composite types you define. So, for example: > > test=# create table foo (f1 int,f2 text); > CREATE TABLE > test=# create table bar (f3 int,f4 text); > CREATE TABLE > test=# create view foobar as select f1,f2,f4 from foo, bar where f1=f3; > CREATE VIEW > test=# insert into foo values(1,'a'); > INSERT 1105496 1 > test=# insert into foo values(2,'b'); > INSERT 1105497 1 > test=# insert into bar values(1,'c'); > INSERT 1105498 1 > test=# insert into bar values(2,'d'); > INSERT 1105499 1 > > -- This uses a named composite type based on the view > test=# create function getfoobar1() returns setof foobar as 'select f1,f2,f4 > from foo, bar where f1=f3' language sql; > CREATE FUNCTION > test=# select * from getfoobar1(); > f1 | f2 | f4 > ----+----+---- > 1 | a | c > 2 | b | d > (2 rows) > > -- This uses an anonymous composite type specified at runtime > test=# create function getfoobar2() returns setof record as 'select f1,f2,f4 > from foo, bar where f1=f3' language sql; > CREATE FUNCTION > test=# select * from getfoobar2() as (f1 int,f2 text,f4 text); > f1 | f2 | f4 > ----+----+---- > 1 | a | c > 2 | b | d > (2 rows) > > HTH, > > Joe > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
