Thread: returning more than one value from a function
Greetings,
I'm attempting to create a PL/PGSQL function with an IF/THEN
conditional. Basically, all I really need is the ability to run a
long/complex SQL query based on the value of the newest row of one
column in a specific table (if its equal to 1 then run the SQL, if
anything else don't run it). In pseudo code, something like:
CREATE OR REPLACE FUNCTION foo0 RETURN text AS $$
IF (SELECT current_status from table0 WHERE id in (SELECT max(id) FROM
table0))='1' THEN
<LONG SQL QUERY>
END IF;
LANGUAGE 'plpgsql' ;
This seems like a fairly simple requirement, yet I can't find any way
to do this without creating a function. The problem is that the
long/complex SQL query needs to return multiple columns of output (not
just a single value), and functions can only return a single
value/column, so I'm a bit stuck on how to make this work.
Is there an alternative solution that I'm missing?
thanks!
On 2010-04-30, Lonni J Friedman <netllama@gmail.com> wrote: > Greetings, > I'm attempting to create a PL/PGSQL function with an IF/THEN > conditional. Basically, all I really need is the ability to run a > long/complex SQL query based on the value of the newest row of one > column in a specific table (if its equal to 1 then run the SQL, if > anything else don't run it). In pseudo code, something like: > > CREATE OR REPLACE FUNCTION foo0 RETURN text AS $$ > IF (SELECT current_status from table0 WHERE id in (SELECT max(id) FROM > table0))='1' THEN > <LONG SQL QUERY> > END IF; > LANGUAGE 'plpgsql' ; > > > This seems like a fairly simple requirement, yet I can't find any way > to do this without creating a function. > The problem is that the > long/complex SQL query needs to return multiple columns of output (not > just a single value), and functions can only return a single > value/column, so I'm a bit stuck on how to make this work. > > Is there an alternative solution that I'm missing? functions can return multple columns. or you could try this: SELECT * FROM ( <long sql query> ) AS foo WHERE ((SELECT current_status from table0 WHERE id in (SELECT max(id) FROM table0))='1') ; the planner should be is smart enough to see that the where clause is independant to the from clause and immutable and so evaluate it once.
On Friday 30. April 2010 18.37.34 Lonni J Friedman wrote: > Greetings, > I'm attempting to create a PL/PGSQL function with an IF/THEN > conditional. Basically, all I really need is the ability to run a > long/complex SQL query based on the value of the newest row of one > column in a specific table (if its equal to 1 then run the SQL, if > anything else don't run it). In pseudo code, something like: > > CREATE OR REPLACE FUNCTION foo0 RETURN text AS $$ > IF (SELECT current_status from table0 WHERE id in (SELECT max(id) FROM > table0))='1' THEN > <LONG SQL QUERY> > END IF; > LANGUAGE 'plpgsql' ; > > > This seems like a fairly simple requirement, yet I can't find any way > to do this without creating a function. The problem is that the > long/complex SQL query needs to return multiple columns of output (not > just a single value), and functions can only return a single > value/column, so I'm a bit stuck on how to make this work. > > Is there an alternative solution that I'm missing? Don't know if you're aware of the CASE WHEN ... ELSE ... END construct, which is vanilla SQL, and is very similar to the usual IF ... THEN ... ELSE. <http://www.postgresql.org/docs/current/static/functions- conditional.html#AEN15201> regards, -- Leif Biberg Kristensen http://solumslekt.org/
Thanks for your reply. Luiz Neri replied privately to me last Friday, and suggested that I use a 'SELECT INTO ...' query inside the function to put the result of the query into a temp table. That seems to be working out ok, with one unfortunate side effect. Now I've got the opposite problem, namely that I can't find a way to have my function not return anything at all. Whenever I try to remove the 'RETURN AS ....' portion of the function, it fails to get created. Is it not possible to have a function that returns nothing? thanks! On Sat, May 1, 2010 at 4:43 AM, Leif Biberg Kristensen <leif@solumslekt.org> wrote: > On Friday 30. April 2010 18.37.34 Lonni J Friedman wrote: >> Greetings, >> I'm attempting to create a PL/PGSQL function with an IF/THEN >> conditional. Basically, all I really need is the ability to run a >> long/complex SQL query based on the value of the newest row of one >> column in a specific table (if its equal to 1 then run the SQL, if >> anything else don't run it). In pseudo code, something like: >> >> CREATE OR REPLACE FUNCTION foo0 RETURN text AS $$ >> IF (SELECT current_status from table0 WHERE id in (SELECT max(id) FROM >> table0))='1' THEN >> <LONG SQL QUERY> >> END IF; >> LANGUAGE 'plpgsql' ; >> >> >> This seems like a fairly simple requirement, yet I can't find any way >> to do this without creating a function. The problem is that the >> long/complex SQL query needs to return multiple columns of output (not >> just a single value), and functions can only return a single >> value/column, so I'm a bit stuck on how to make this work. >> >> Is there an alternative solution that I'm missing? > > Don't know if you're aware of the CASE WHEN ... ELSE ... END construct, which > is vanilla SQL, and is very similar to the usual IF ... THEN ... ELSE. > <http://www.postgresql.org/docs/current/static/functions- > conditional.html#AEN15201> > > regards, > -- > Leif Biberg Kristensen > http://solumslekt.org/ > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand https://netllama.linux-sxs.org
Lonni J Friedman <netllama@gmail.com> writes:
> ... Now I've got the
> opposite problem, namely that I can't find a way to have my function
> not return anything at all. Whenever I try to remove the 'RETURN AS
> ....' portion of the function, it fails to get created. Is it not
> possible to have a function that returns nothing?
Declare it as RETURNS VOID. This is really just window dressing, as
the actual behavior is to return a null value, but it documents your
intention.
regards, tom lane
On Sat, May 1, 2010 at 3:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Lonni J Friedman <netllama@gmail.com> writes: >> ... Now I've got the >> opposite problem, namely that I can't find a way to have my function >> not return anything at all. Whenever I try to remove the 'RETURN AS >> ....' portion of the function, it fails to get created. Is it not >> possible to have a function that returns nothing? > > Declare it as RETURNS VOID. This is really just window dressing, as > the actual behavior is to return a null value, but it documents your > intention. thanks, that is exactly what I needed.