Postgres Pro
Downloads
Home   >   mailing lists

Thread: EXISTS

EXISTS

From
Denis Woodbury
Date:
Hi,

I would like to know if this this type of statement can be used in
Postgresql

IF NOT EXISTS (SELECT 1 FROM Table WHERE col1 = 'mystring' )
BEGIN
...
END

If it can, any idea why I get this error.
********** Error **********

ERROR: syntax error at or near "IF"
SQL state: 42601
Character: 2


Thanks


denis woodbury 
denis@woodmic.net
450-242-0249

Re: EXISTS

From
"Leif B. Kristensen"
Date:
On Saturday 11. October 2008, Denis Woodbury wrote:
>Hi,
>
>I would like to know if this this type of statement can be used in
>Postgresql
>
>IF NOT EXISTS (SELECT 1 FROM Table WHERE col1 = 'mystring' )
>BEGIN
>...
>END
>
>If it can, any idea why I get this error.
>********** Error **********
>
>ERROR: syntax error at or near "IF"
>SQL state: 42601
>Character: 2

CASE IF NOT (SELECT 1 FROM Table WHERE col1 = 'mystring' )
THEN BEGIN
...
END
[ELSE BEGIN ... END];

-- 
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/

Re: EXISTS

From
Tom Lane
Date:
Denis Woodbury <denis@woodmic.net> writes:
> I would like to know if this this type of statement can be used in
> Postgresql

> IF NOT EXISTS (SELECT 1 FROM Table WHERE col1 = 'mystring' )
> BEGIN
> ...
> END

I suspect you are trying to type that directly into SQL.
You need to be using plpgsql in order to use procedural
logic (ie, if/then).
        regards, tom lane

Re: EXISTS

From
Craig Ringer
Date:
Denis Woodbury wrote:
> Hi,
> 
> I would like to know if this this type of statement can be used in
> Postgresql
> 
> IF NOT EXISTS (SELECT 1 FROM Table WHERE col1 = 'mystring' )
> BEGIN
> ...
> END

PostgreSQL doesn't have any sort of free block flow control; it doesn't 
have an IF statement or similar in SQL. You can usually just create a 
PL/PgSQL function to do what you want.

It'd occasionally be nice to be able to write PL/PgSQL bodies in-line in 
SQL code rather than explicitly creating then dropping a function when 
you do need to do something a bit weird (usually in admin/maintenance 
scripts) but the current approach does work fine.

It also helps that you can often achieve the required logic with plain, 
standard SQL. The CASE statement is particularly useful:

SELECT  CASE    WHEN col1 = 'mystring' THEN [expression or function call]  END
FROM Table;

--
Craig Ringer

Re: EXISTS

From
Denis Woodbury
Date:
Thanks to those who responded, I see PL/PgSQL is the way to go

Regards, denis woodbury



on 10/11/08 1:32 PM, [NAME] at [ADDRESS] wrote:

> Denis Woodbury <denis@woodmic.net> writes:
>> I would like to know if this this type of statement can be used in
>> Postgresql
> 
>> IF NOT EXISTS (SELECT 1 FROM Table WHERE col1 = 'mystring' )
>> BEGIN
>> ...
>> END
> 
> I suspect you are trying to type that directly into SQL.
> You need to be using plpgsql in order to use procedural
> logic (ie, if/then).
> 
> regards, tom lane