Re: sql script confusion

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=substring(var_relname, "0 until _")
>       set var_fieldname=substring(var_relname,"first _ until 2nd _")
>       select var_maxID=max(var_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