Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question about SQL Control Structure(if then, for loop)

Question about SQL Control Structure(if then, for loop)

From
Jay Chiu
Date:
Hello,

I am new to PostgreSQL and want to use the IF-THEN; LOOP
statements in my SQL command file. Thus I may use psql -f cfile
to exceute.

However I always got syntax error on if/for in psql. For
example: 
RDM=# for i in 1 .. 10 loop
RDM-# select "test"
RDM-# end loop;
ERROR:  syntax error at or near "for" at character 1
LINE 1: for i in 1 .. 10 loop

RDM=# if exits ( select * from testtable)
RDM-# then
RDM-# select "TEST"
RDM-# ;
ERROR:  syntax error at or near "if" at character 1
LINE 1: if exits ( select * from testtable)       ^

Can someone tell me how to use if/for in psql? The document
shows the control structures in the PL/pgSQL section. Must I use
if/for inside some procedure/function?

Thanks a lot.

Jay


________________________________________________
Get your own "800" number
Voicemail, fax, email, and a lot more
http://www.ureach.com/reg/tag

Re: Question about SQL Control Structure(if then, for loop)

From
"A. Kretschmer"
Date:
am  16.05.2006, um 21:51:45 -0400 mailte Jay Chiu folgendes:
> Can someone tell me how to use if/for in psql? The document

In plain SQL isn't if, then, else and so on.


> shows the control structures in the PL/pgSQL section. Must I use
> if/for inside some procedure/function?

Yes.


HTH, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    ===

Re: Question about SQL Control Structure(if then, for loop)

From
"Michael Artz"
Date:

If you're control is that simple, you can write similar statements in pure SQL:

RDM=# for i in 1 .. 10 loop
RDM-# select "test"
RDM-# end loop;
ERROR:  syntax error at or near "for" at character 1
LINE 1: for i in 1 .. 10 loop

SELECT 'test' FROM


RDM=# if exits ( select * from testtable)
RDM-# then
RDM-# select "TEST"
RDM-# ;
ERROR:  syntax error at or near "if" at character 1
LINE 1: if exits ( select * from testtable)
        ^

 SELECT 'test' FROM testtable LIMIT 1;

Without knowing about what you want to do, I can't guarantee that that will suffice and/or be efficient.  If it gets much more complicated, you might have to go to some procedural language (PL/PGSQL, PL/Perl, etc).  Just remember that SQL is set-based, not procedural.

-Mike

Re: Question about SQL Control Structure(if then, for loop)

From
"Michael Artz"
Date:

SELECT 'test' FROM

SELECT 'test' FROM generate_series(1,10);

Sorry, thought faster than I could type :)

-Mike