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
