Thread: Passing result of multirow subquery to C function
During the holidays I've taken on to looking at writing a postgres C extension/function. What I've done so far is more or less the article series by Ron Peterson (http://linuxgazette.net/142/peterson.html). What I'm having issues figuring out is how to pass the results of a subquery to a function (if at all possible?): SELECT hello((SELECT name FROM names)); Where name is of type TEXT. And results from calling the function is: hello ------------ Hello, Benny Hello, Kenny And what the function does is to only prepend "Hello, " to the string. My naive attempt errors with: ERROR: more than one row returned by a subquery used as an expression What should I be looking at?
Select hello(name) from names You can only pass a single row to a function. A work around is to use arrays but for your example the above form is what you would do and is typical of function usage in a table context. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Passing-result-of-multirow-subquery-to-C-function-tp5784914p5784915.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Magnus Persson <magnus.e.persson@gmail.com> writes:
> What I'm having issues figuring out is how to pass the results of a
> subquery to a function (if at all possible?):
> SELECT hello((SELECT name FROM names));
There's no direct way to do that; we have a notion of a "function
returning set", but not one of a "function accepting set".
The most straightforward thing is to reinterpret the requirement
as
SELECT hello(name) FROM names;
so that the function just deals with one name at a time.
You could also give the query to the function as a string and
have it execute the query internally (using SPI):
SELECT hello('SELECT name FROM names');
I find this to be a pretty bad design choice most of the time,
but sometimes there's no good alternative. There are precedents
in core PG, such as the ts_stat() functions.
regards, tom lane
On Tue, Dec 31, 2013 at 10:24 AM, David Johnston <polobo@yahoo.com> wrote: > Select hello(name) from names > > You can only pass a single row to a function. A work around is to use > arrays but for your example the above form is what you would do and is > typical of function usage in a table context. for posterity, you could do arrays with, SELECT hello(array(select name from names)); if 'name' is text, the function would take TEXT[]. you could also pass the entire table via: SELECT hello(array(select n from names n)); in which case the hello function would take names[] and you could pass through the data in the receiving function with FOREACH or unnest(). This type of invocation is OK, I use it often, but will not scale very well to large amounts of data passed in to the function. If you need industrial passing handling between functions TEMP tables (perhaps decorated with ON COMMIT DROP) are probably the best way to go but require some extra care to manage scope and will be slower for the 'just a few records' case. Yet another way to do it is with refcursors which are basically cursors you can refer to by string. If you do use the convention: SELECT func(col) FROM foo; I would advise making func() an IMMUTABLE plpgsql function or (even better) a sql function if it supports inlining. merlin
Merlin, Thank you for that explanation of passing arrays to a function - Gerald On 1/2/2014 12:33 PM, Merlin Moncure wrote: > On Tue, Dec 31, 2013 at 10:24 AM, David Johnston <polobo@yahoo.com> wrote: >> Select hello(name) from names >> >> You can only pass a single row to a function. A work around is to use >> arrays but for your example the above form is what you would do and is >> typical of function usage in a table context. > for posterity, you could do arrays with, > > SELECT hello(array(select name from names)); > > if 'name' is text, the function would take TEXT[]. you could also pass > the entire table via: > > SELECT hello(array(select n from names n)); > > in which case the hello function would take names[] and you could pass > through the data in the receiving function with FOREACH or unnest(). > This type of invocation is OK, I use it often, but will not scale very > well to large amounts of data passed in to the function. If you need > industrial passing handling between functions TEMP tables (perhaps > decorated with ON COMMIT DROP) are probably the best way to go but > require some extra care to manage scope and will be slower for the > 'just a few records' case. > > Yet another way to do it is with refcursors which are basically > cursors you can refer to by string. If you do use the convention: > > SELECT func(col) FROM foo; > > I would advise making func() an IMMUTABLE plpgsql function or (even > better) a sql function if it supports inlining. > > merlin > > -- siamo arrivati sani e salvi