CODEWITHSUNDEEP
postgresqlsequence
Oleg
Oleg

Reputation: 403

PostgreSQL ALTER SEQUENCE in function

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?

Upvotes: 3

Views: 3074

Answers (3)

araqnid
araqnid

Reputation: 133722

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

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

Upvotes: 5

Milen A. Radev
Milen A. Radev

Reputation: 62653

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;

Upvotes: 0

Tometzky
Tometzky

Reputation: 23910

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.

Upvotes: 0

Related Questions