EXISTS

[Denis Woodbury]

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@...
450-242-0249




--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Leif B. Kristensen]

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/

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Tom Lane-2]

Denis Woodbury <denis@...> 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

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Craig Ringer]

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

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Denis Woodbury]

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@...> 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

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql