Re: 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(at)gmail(dot)com>:
> 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(at)postgresql(dot)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(at)svana(dot)org> http://svana.org/kleptog/
> 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(at)gmail(dot)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(dot)t(dot)wilson(at)gmail(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)com> writes:
> On Mon, May 18, 2009 at 3:13 PM, Joshua Berry <yoberi(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Mon, May 18, 2009 at 3:13 PM, Joshua Berry <yoberi(at)gmail(dot)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