Providing an alternative result when there is no result
Hello all,
Is there an easy and efficient way to return a boolean false for a
query that returns no result, and true for one that does return a
result?
Currently we select the result into a temp table.
SELECT INTO temp_table id FROM ... ;
IF temp_table IS NULL THEN
resp:= 'NO';
ELSE
resp:= 'YES';
END IF;
I'd like to combine this into one step like this:
SELECT
CASE
WHEN id is null THEN 'NO'::text
ELSE 'YES'::text
END
FROM ...;
But, this is not have SELECT's work, I suppose. The CASE is never
encountered when there is no result, so in the "NO" case, NULL is
returned.
Any hints/tips? Is our original solution okay, or is there something
we can do to improve things?
Thanks,
Joshua Berry
Hello
look on GET DIAGNOSTIC statement or FOUND variable
CREATE OR REPLACE FUNCTION foo()
RETURNS boolean AS $$
BEGIN
SELECT INTO temp_table ...
RETURN found;
END;
$$ language plpgsql;
regards
Pavel Stehule
2009/5/18 Joshua Berry <yoberi@gmail.com>:
Show quoted text
Hello all,
Is there an easy and efficient way to return a boolean false for a query
that returns no result, and true for one that does return a result?Currently we select the result into a temp table.
SELECT INTO temp_table id FROM ... ;
IF temp_table IS NULL THEN
resp:= 'NO';
ELSE
resp:= 'YES';
END IF;I'd like to combine this into one step like this:
SELECT
CASE
WHEN id is null THEN 'NO'::text
ELSE 'YES'::text
END
FROM ...;But, this is not have SELECT's work, I suppose. The CASE is never
encountered when there is no result, so in the "NO" case, NULL is returned.Any hints/tips? Is our original solution okay, or is there something we can
do to improve things?Thanks,
Joshua Berry
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, 2009-05-18 at 15:13 -0400, Joshua Berry wrote:
Is there an easy and efficient way to return a boolean false for a
query that returns no result, and true for one that does return a
result?
Presuming that you're not using the values in temp_table, I think you
should be using PERFORM * WHERE ...; and then IF FOUND ... ELSE ... END
IF;
See here:
http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html
and also follow link to 38.5.5 .
-Reece
On Mon, May 18, 2009 at 03:13:56PM -0400, Joshua Berry wrote:
Hello all,
Is there an easy and efficient way to return a boolean false for a query
that returns no result, and true for one that does return a result?Currently we select the result into a temp table.
SELECT INTO temp_table id FROM ... ;
What might work is:
SELECT EXISTS(subquery);
As in:
SELECT EXISTS( SELECT 1 WHERE true );
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
On Mon, May 18, 2009 at 3:13 PM, Joshua Berry <yoberi@gmail.com> wrote:
Any hints/tips? Is our original solution okay, or is there something we can
do to improve things?
It seems as if you don't really care about the results of the query-
just whether or not it returns any rows. In that case, why not
something like:
select (case when exists (select * from foo where...) then true else
false end) as result;
--
- David T. Wilson
david.t.wilson@gmail.com
On Mon, May 18, 2009 at 03:13:56PM -0400, Joshua Berry wrote:
Hello all,
Is there an easy and efficient way to return a boolean false for a query
that returns no result, and true for one that does return a result?Currently we select the result into a temp table.
SELECT INTO temp_table id FROM ... ;
IF temp_table IS NULL THEN
resp:= 'NO';
ELSE
resp:= 'YES';
END IF;
SELECT EXISTS (SELECT 1 FROM ....);
will get you a boolean which can't be NULL. You can either map that
to "yes/no" or return it as is.
Hope this helps :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Mon, May 18, 2009 at 3:13 PM, Joshua Berry <yoberi@gmail.com> wrote:
Hello all,
Is there an easy and efficient way to return a boolean false for a query
that returns no result, and true for one that does return a result?
Probably the best general approach is to:
select count(*) = 1 from
(
<query> limit 1
)q;
the point being that in some cases (not all obviously) the limit 1 can
be a huge win, as you only care if there are rows or not. with little
work (you have to be aware of if/when you can tack 'limit 1 onto a
query) you could generalize it into a pl/pgsql dynamic sql function
taking a query string.
merlin
Merlin Moncure <mmoncure@gmail.com> writes:
On Mon, May 18, 2009 at 3:13 PM, Joshua Berry <yoberi@gmail.com> wrote:
Is there an easy and efficient way to return a boolean false for a query
that returns no result, and true for one that does return a result?
Probably the best general approach is to:
select count(*) = 1 from
(
<query> limit 1
)q;
Seems like EXISTS() is much more straightforward ...
the point being that in some cases (not all obviously) the limit 1 can
be a huge win, as you only care if there are rows or not.
... the system does know about optimizing EXISTS as if it were a LIMIT
query; you don't need to tack that on yourself.
regards, tom lane
On Tue, May 19, 2009 at 7:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Merlin Moncure <mmoncure@gmail.com> writes:
On Mon, May 18, 2009 at 3:13 PM, Joshua Berry <yoberi@gmail.com> wrote:
Is there an easy and efficient way to return a boolean false for a query
that returns no result, and true for one that does return a result?Probably the best general approach is to:
select count(*) = 1 from
(
<query> limit 1
)q;Seems like EXISTS() is much more straightforward ...
yes...I didn't notice david's response upthread. that is indeed very elegant.
merlin