Skip site navigation (1) Skip section navigation (2)

Re: How to use the "setof" of CREATE FUNCTION

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Fabien Thiriet <fabien(at)freever(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org, andre(at)freever(dot)com
Subject: Re: How to use the "setof" of CREATE FUNCTION
Date: 2000-08-30 18:21:00
Message-ID: 20291.967659660@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Fabien Thiriet <fabien(at)freever(dot)com> writes:
> CREATE FUNCTION foo(varchar) RETURNS setof myTable
> AS 'UPDATE .......;
> INSERT.......;
> SELECT myTable.field2 from myTable'
> LANGUAGE 'sql';

> I always get an error saying that there is a type mismatch between what is
> behing the "setof" and what is return by this function (myTable.field2)

Well, yeah: you declared the function to return a set of the tuple
datatype myTable, not a set of whatever field2's datatype is.
Perhaps you wanted

CREATE FUNCTION foo(varchar) RETURNS setof myTable
AS 'UPDATE .......;
INSERT.......;
SELECT * from myTable'
LANGUAGE 'sql';

which hands back the entire table.  Alternatively, if you do want to
return just the one column, you should declare the function to return
setof whatever-type-field2-is.

Note that functions returning sets are not as useful as they should be,
because you can only call them in limited places (at the top level of
a SELECT-list item, IIRC).  Functions returning tuples are not as
useful as they should be either, because you can't do anything with
the result except select out an individual column; worse, there's this
bizarre syntax for it --- you can't write the obvious foo(x).bar,
for some reason, but have to do x.foo.bar, which only works for simple
field-of-a-relation arguments.  Ugh.  This whole area needs work.

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: Mark HollomonDate: 2000-08-30 18:29:56
Subject: Re: New relkind for views
Previous:From: Mario WeilguniDate: 2000-08-30 18:04:33
Subject: Patch for TNS services

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group