Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group