Re: ALTER SEQUENCE ... RESTART WITH [variable] problem

I need to generate a couple of dozen statements reseting my sequences
so that they're next values are greater than the biggest existing ids.
The problem is, I can't even form a statement to update one sequence.
This is what I tried:

CREATE OR REPLACE FUNCTION init_sequences() RETURNS void AS
$BODY$
DECLARE
next_id_table1 INTEGER;
BEGIN
SELECT INTO next_id_table1 MAX(id)+1 FROM table1;
ALTER SEQUENCE pk_table1 RESTART next_id_table1;
END;
$BODY$
LANGUAGE 'plpgsql';

The problem seems to be the ALTER statement:

ERROR: syntax error at or near "$1" at character 36
QUERY: ALTER SEQUENCE pk_table1 RESTART $1
CONTEXT: SQL statement in PL/PgSQL function "init_sequences" near line 5

If I change the ALTER statement like this
ALTER SEQUENCE pk_table1 RESTART 200;
it works. But is obviously not what I wanted.
Is there a way to get the ALTER SEQUENCE statement to use a value
stored in a variable?

t.n.a.

On Sat, May 20, 2006 at 09:52:29AM +0200, Tomi NA wrote:
> I need to generate a couple of dozen statements reseting my sequences
> so that they're next values are greater than the biggest existing ids.
> The problem is, I can't even form a statement to update one sequence.
> This is what I tried:

<snip>

> The problem seems to be the ALTER statement:
>
> ERROR: syntax error at or near "$1" at character 36
> QUERY: ALTER SEQUENCE pk_table1 RESTART $1
> CONTEXT: SQL statement in PL/PgSQL function "init_sequences" near line 5

Seems you can't use a variable there. Your choices are to build a
string and use EXECUTE, or just do:

SELECT setval('sequence',value);

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On 5/20/06, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:

> Seems you can't use a variable there. Your choices are to build a
> string and use EXECUTE, or just do:
>
> SELECT setval('sequence',value);

The EXECUTE string solution did the job. Thank you very much, Martijn.

t.n.a.

On Sat, May 20, 2006 at 11:46:25AM +0200, Tomi NA wrote:
> On 5/20/06, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
>
> >Seems you can't use a variable there. Your choices are to build a
> >string and use EXECUTE, or just do:
> >
> >SELECT setval('sequence',value);
>
> The EXECUTE string solution did the job. Thank you very much, Martijn.

As an FYI, the function version would be faster, since it can cache the
plan. Though in this case it sounds like it doesn't matter one iota.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461