sql script confusion

[Sim Zacks]

I am in the process of testing PostGreSQL for our sales and product
database and I am a little confused about SQL scripting and various
available languages. (My background is mostly MSSQL Server).

I am using the PGAdmin tool and I would like to write a script to
update all sequences to the current max value in the table.

Do I need to use a specific language in a function to do this or does
it work as native SQL, as it would in T-SQL?

What I would like to do is something like -
(pseudo code)
declare cursor for select relname from pg_statio_user_ sequences
open cursor
fetch next into var_relname
while not cursor.eof
set var_tblname=sub string(var_reln ame, "0 until _")
set var_fieldname=s ubstring(var_re lname,"first _ until 2nd _")
select var_maxID=max(v ar_fieldname) from var_tblname
ALTER SEQUENCE var_relname
RESTART WITH var_maxID+1;
fetch next into var_relname
end loop

Can dynamic statements be written in "raw sql" or do they need to be
encompassed in a language? Do all language scripts have to be
functions or can I do something like:
Start Language Processing Here
....Code
End Language Processing
Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Richard Huxton]

Sim Zacks wrote:

Do I need to use a specific language in a function to do this or does
it work as native SQL, as it would in T-SQL?
You need to use a specific language.
What I would like to do is something like -
(pseudo code)
declare cursor for select relname from pg_statio_user_ sequences
open cursor
fetch next into var_relname
while not cursor.eof
set var_tblname=sub string(var_reln ame, "0 until _")
set var_fieldname=s ubstring(var_re lname,"first _ until 2nd _")
select var_maxID=max(v ar_fieldname) from var_tblname
ALTER SEQUENCE var_relname
RESTART WITH var_maxID+1;
fetch next into var_relname
end loop
For this plpgsql might be a good choice. Think sql with a few
loop/control structures and variables.
Can dynamic statements be written in "raw sql" or do they need to be
encompassed in a language? Do all language scripts have to be
functions or can I do something like:
Start Language Processing Here
...Code
End Language Processing

You need to create a function and then call it. You can create dynamic
SQL as a string and then EXECUTE it with plpgsql. The other languages
offer various ways too.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly