Thread: using a selected row as a function parameter
Hi all, I've been struggling with this for a while and haven't found anything on the 'Net about it. I've created a function that takes two table rows as a parameters. I'd like to use the output of a select (two single rows) as the parameters, but I can't get it to work. What am I missing? The function prototype looks like this: CREATE OR REPLACE FUNCTION queue.apply_routing_rule( queue.messages, queue.routing_rules ) RETURNS int2 and I would like to call it like this: SELECT queue.apply_routing_rule( (SELECT * from queue.messages WHERE id = 1), (SELECT* from queue.routing_rules WHERE id = 1) ); I get an error message along the lines of "sub-query must return a single value". I've tried different combinations of "CAST" and "ROW" functions, but they give syntax errors. Any suggestions? Regards, Ami.
Ami Ganguli <ami.ganguli@gmail.com> writes: > SELECT queue.apply_routing_rule( > (SELECT * from queue.messages WHERE id = 1), > (SELECT * from queue.routing_rules WHERE id = 1) > ); Not sure if that particular syntax should be expected to work, but why not SELECT queue.apply_routing_rule(messages.*, routing_rules.*) FROM queue.messages, queue.routing_rules WHERE messages.id = 1 AND routing_rules.id = 1; The query as you want to write it will certainly fail anyway if there's more than one row with id = 1 in either table, so it's not like there's some huge inefficiency in doing it as a join. regards, tom lane
On Fri, Jun 03, 2005 at 05:44:59PM +0300, Ami Ganguli wrote: > > SELECT queue.apply_routing_rule( > (SELECT * from queue.messages WHERE id = 1), > (SELECT * from queue.routing_rules WHERE id = 1) > ); > > I get an error message along the lines of "sub-query must return a > single value". The error I get is "subquery must return only one column". Queries like the following should work in 8.0.x: SELECT queue.apply_routing_rule(m, r) FROM (SELECT * FROM queue.messages WHERE id = 1) AS m, (SELECT * FROM queue.routing_rules WHERE id = 1) AS r; SELECT queue.apply_routing_rule(m, r) FROM queue.messages AS m, queue.routing_rules AS r WHERE m.id = 1 AND r.id = 1; -- Michael Fuhr http://www.fuhr.org/~mfuhr/