returning count(*) when it is > 1, else -1

From: Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)ar>
To: postgres list <pgsql-sql(at)postgresql(dot)org>
Subject: returning count(*) when it is > 1, else -1
Date: 2008-10-17 11:45:23
Message-ID: 48F87AD3.6000206@fmed.uba.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi all. Im triyng to implement this in plain sql.
The only thing i have working is

select case when (select count(*) from test where id=$1 )
> 0 then (select count(*) from test where id=$1)
else -1
end;

But it does a doble count(*) that i must avoid.
I cant refer to the 'first' count like
select case when (select count(*) from test where id=$1 ) AS total
> 0 then total
else -1
end;

Because i have "Syntax error near AS"

I have a plpgsql version of this, but i swear to my boss that it can be
done is plain sql. Please tell me that im right :)

Thanks!
Gerardo

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Pavel Stehule 2008-10-17 12:00:55 Re: returning count(*) when it is > 1, else -1
Previous Message Oliveiros Cristina 2008-10-16 14:02:59 Re: Search fields in multiple tables