pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedusing a selected row as a function parameter
3+ messages / 3 participants
[nested] [flat]
* using a selected row as a function parameter
@ 2005-06-03 14:44 Ami Ganguli <ami.ganguli@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Ami Ganguli @ 2005-06-03 14:44 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: using a selected row as a function parameter
@ 2005-06-03 15:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ami Ganguli <ami.ganguli@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-06-03 15:15 UTC (permalink / raw)
To: Ami Ganguli <ami.ganguli@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: using a selected row as a function parameter
@ 2005-06-03 15:23 Michael Fuhr <mike@fuhr.org>
parent: Ami Ganguli <ami.ganguli@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2005-06-03 15:23 UTC (permalink / raw)
To: Ami Ganguli <ami.ganguli@gmail.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-06-03 15:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-03 14:44 using a selected row as a function parameter Ami Ganguli <ami.ganguli@gmail.com>
2005-06-03 15:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-03 15:23 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox