Re: [GENERAL] Providing an alternative result when there is no result
On Tue, May 19, 2009 at 7:00 PM, Tom Lane wrote: > Merlin Moncure writes: >> On Mon, May 18, 2009 at 3:13 PM, 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? > >> Probably the best general approach is to: > >> select count(*) = 1 from >> ( >> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Providing an alternative result when there is no result
Merlin Moncure writes: > On Mon, May 18, 2009 at 3:13 PM, 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? > Probably the best general approach is to: > select count(*) = 1 from > ( >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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Providing an alternative result when there is no result
On Mon, May 18, 2009 at 3:13 PM, 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? > Probably the best general approach is to: select count(*) = 1 from ( 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Providing an alternative result when there is no result
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 http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Providing an alternative result when there is no result
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
Re: [GENERAL] Providing an alternative result when there is no result
On Mon, May 18, 2009 at 3:13 PM, Joshua Berry 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.wil...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Providing an alternative result when there is no result
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 http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Providing an alternative result when there is no result
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 : > 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 > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general