Fabien Thiriet <fabien(at)freever(dot)com> writes:
> CREATE FUNCTION foo(varchar) RETURNS setof myTable
> AS 'UPDATE .......;
> 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 .......;
SELECT * from myTable'
which hands back the entire table. Alternatively, if you do want to
return just the one column, you should declare the function to return
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 Hollomon||Date: 2000-08-30 18:29:56|
|Subject: Re: New relkind for views|
|Previous:||From: Mario Weilguni||Date: 2000-08-30 18:04:33|
|Subject: Patch for TNS services|