Обсуждение: Conditional Statement

Hello,
I am trying to write a piece of SQL code, which will first check if a
table exist, drop it and then recreate it. I didn't see any 'IF' syntax
in pgsql. Is there any other alternative? I tried to use 'case'
structure, but didn't succeed.
Thanks and regards.
-Samik

PG's PL/pgSQL language does indeed support conditional statements.
See PostgreSQL 7.x Programmer's Guide, Procedural Languages, Description
section with your installation.

In fact it supports

IF-THEN
IF-THEN-ELSE
IF-THEN-ELSE IF

However, what I have seen people do in a case of just drop-create is to
simply
drop table; create table. So if the table is not there, you just get an
error.
But your request is valid, what if one wants a better control of what to
create
and what to leave alone.

I think the standard front end (psql(1)) does understand SQL but I'm not
sure
if it also understand PL/pgSQL...perhaps someone can add to this....

Samik Raychauhduri wrote:

> Hello,
> I am trying to write a piece of SQL code, which will first check if a
> table exist, drop it and then recreate it. I didn't see any 'IF' syntax
> in pgsql. Is there any other alternative? I tried to use 'case'
> structure, but didn't succeed.
> Thanks and regards.
> -Samik
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
-------------------------------------------------------------------------
Medi Montaseri                               medi@CyberShell.com
Unix Distributed Systems Engineer            HTTP://www.CyberShell.com
CyberShell Engineering
-------------------------------------------------------------------------

Hi,
Thanks a lot for the pointer. But it seems I can't run these commands
through psql. Specifically, I am trying to run the following file
through psql, and getting the errors as below. I used to do similar
stuff in Sybase.

---- junk.sql ----
BEGIN
         IF EXISTS(SELECT * FROM pg_tables WHERE tablename='junk') = 't'
    THEN
                 DROP TABLE junk;
                 CREATE TABLE junk(a varchar(10));
                 INSERT INTO junk VALUES('junk junk');
         END IF;
END;
-----------------

$ psql -f junk.sql
psql:junk.sql:3: ERROR:  parser: parse error at or near "if"
psql:junk.sql:4: ERROR:  Relation 'junk' already exists
psql:junk.sql:6: ERROR:  parser: parse error at or near "if"
psql:junk.sql:7: NOTICE:  COMMIT: no transaction in progress
COMMIT

Waiting for further comments on this regard.
Thanks.
-Samik


Medi Montaseri wrote:

> PG's PL/pgSQL language does indeed support conditional statements.
> See PostgreSQL 7.x Programmer's Guide, Procedural Languages, Description
> section with your installation.
>
> In fact it supports
>
> IF-THEN
> IF-THEN-ELSE
> IF-THEN-ELSE IF
>
> However, what I have seen people do in a case of just drop-create is to
> simply
> drop table; create table. So if the table is not there, you just get an
> error.
> But your request is valid, what if one wants a better control of what to
> create
> and what to leave alone.
>
> I think the standard front end (psql(1)) does understand SQL but I'm not
> sure
> if it also understand PL/pgSQL...perhaps someone can add to this....
>
> Samik Raychauhduri wrote:
>
>
>>Hello,
>>I am trying to write a piece of SQL code, which will first check if a
>>table exist, drop it and then recreate it. I didn't see any 'IF' syntax
>>in pgsql. Is there any other alternative? I tried to use 'case'
>>structure, but didn't succeed.
>>Thanks and regards.
>>-Samik

Samik Raychaudhuri <samik@cae.wisc.edu> writes:

> Hi,
> Thanks a lot for the pointer. But it seems I can't run these commands
> through psql. Specifically, I am trying to run the following file
> through psql, and getting the errors as below. I used to do similar
> stuff in Sybase.

You need to define a PL/pgSQL function that contains your code.
'psql' takes straight SQL code, which does not support IF/THEN/ELSE.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

> Hello,
> I am trying to write a piece of SQL code, which will first check if a
> table exist, drop it and then recreate it. I didn't see any 'IF' syntax
> in pgsql. Is there any other alternative? I tried to use 'case'
> structure, but didn't succeed.
> Thanks and regards.
> -Samik
>

Is there reason the table must not exist before you drop it?

When I write SQL scripts (to be fed in to psql) I just go ahead
and drop the table (whether it is really there or not) and if it
does not exist, there is a warning, but the script keeps running.

"Samik Raychauhduri" <samik@cae.wisc.edu> wrote in message news:3C82CA4C.6030205@cae.wisc.edu...
> I am trying to write a piece of SQL code, which will first check if a
> table exist, drop it and then recreate it. I didn't see any 'IF' syntax
> in pgsql. Is there any other alternative? I tried to use 'case'
> structure, but didn't succeed.

The way you do that is to say:

drop table foo
create table foo ...


That's all you need. Nothing fancy. The 'drop table' will print a message
if the table didn't exist already, but it doesn't matter.


Marshall