PostgreSQL ALTER SEQUENCE in function

Question

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?

mu is too short · Accepted Answer · 2012-01-25 19:10:32Z

5

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

SELECT pg_catalog.setval('aaa_id_seq'::regclass, maxid, false);

edited Jan 25, 2012 at 19:10

mu is too short

436k71 gold badges863 silver badges822 bronze badges

answered Jan 25, 2012 at 10:55

araqnid

135k25 gold badges163 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josip Rodin Over a year ago

You usually need that to be PERFORM, and that last false needs to go, otherwise you have an off-by-one error.

Tometzky · Accepted Answer · 2012-01-25 10:32:28Z

0

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.

answered Jan 25, 2012 at 10:32

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

2 Comments

Oleg Over a year ago

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...

Tometzky Over a year ago

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".

Milen A. Radev · Accepted Answer · 2012-01-25 10:45:42Z

0

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;

answered Jan 25, 2012 at 10:45

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Collectives™ on Stack Overflow

PostgreSQL ALTER SEQUENCE in function

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related