current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I have trigger function:

CREATE OR REPLACE FUNCTION update_aaa() RETURNS TRIGGER AS $$
DECLARE maxid INTEGER;
BEGIN
    SELECT MAX(id) INTO maxid FROM aaa;
    ALTER SEQUENCE aaa_id_seq RESTART WITH maxid;
END;
$$ LANGUAGE plpgsql;

And have error:

ERROR:  syntax error at or near "$1"
Line 1: ALTER SEQUENCE aaa_id_seq RESTART WITH  $1

Why $1 ?
What error?

share|improve this question
    
postgres 8.4.10 –  Oleg Jan 25 '12 at 10:30

3 Answers 3

up vote 3 down vote

Maybe use the setval function rather than alter sequence ... restart with?

SELECT pg_catalog.setval('aaa_id_seq'::regclass, maxid, false);
share|improve this answer
    
Thank you! It's work! –  Oleg Jan 25 '12 at 11:26
up vote 0 down vote

Your table is probably empty so

SELECT MAX(id) INTO maxid FROM aaa;

returns NULL;

Change the query to

SELECT COALESCE(MAX(id), <some_appropriate_value>) INTO maxid FROM aaa;
share|improve this answer
    
No, this don't work. Pay attention psql say "Syntax error". –  Oleg Jan 25 '12 at 10:51
    
It works fine here, though my Postgres is 9.1.x. –  Milen A. Radev Jan 25 '12 at 11:00
up vote 0 down vote

I think you need to use EXECUTE for data definition commands (like ALTER) in PL/pgSQL. And you need to LOCK TABLE aaa IN SHARE MODE; before calculating MAX(id) to prevent concurrent changes to table data.

share|improve this answer
    
Hmm... if I wrote: CREATE OR REPLACE FUNCTION update_aaa() RETURNS TRIGGER AS $$ DECLARE maxid INTEGER; BEGIN SELECT MAX(id) INTO maxid FROM aaa; ALTER SEQUENCE aaa_id_seq RESTART WITH 999; END; $$ LANGUAGE plpgsql; All good... –  Oleg Jan 25 '12 at 10:43
    
Because you did not use any variable in your statement and PL/pgSQL parser was not confused. Maybe I should write "dynamic data definition commands". –  Tometzky Jan 25 '12 at 10:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.