Skip site navigation (1) Skip section navigation (2)

Re: sql script confusion

From: Richard Huxton <dev(at)archonet(dot)com>
To: Sim Zacks <sim(at)compulab(dot)co(dot)il>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: sql script confusion
Date: 2004-09-29 09:44:15
Message-ID: 415A83EF.506@archonet.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Richard HuxtonDate: 2004-09-29 09:46:31
Subject: Re: Multiple Rules :: Postgres Is confused !!
Previous:From: Najib Abi FadelDate: 2004-09-29 09:33:10
Subject: Multiple Rules :: Postgres Is confused !!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group