you need to use EXECUTE to do the dynamic lock table.
sql = 'LOCK TABLE ' || sequence_name || 'IN ACCESS EXCLUSIVE MODE';
EXECUTE sql;
Thank you for your help ;-)
I've been able to rewrite my procedure as follows :
---------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION seq_min(sequence_name varchar, minval
integer) RETURNS VOID AS $$
DECLARE
current_seq integer;
BEGIN
EXECUTE 'LOCK TABLE ' || sequence_name || ' IN ACCESS EXCLUSIVE MODE';
current_seq := last_value FROM sequence_name;
IF current_seq < minval THEN
EXECUTE 'ALTER SEQUENCE ' || sequence_name || ' RESTART WITH ' ||
minval;
END IF;
END;
$$ LANGUAGE plpgsql;
---------------------------------------------------------------------------------------------------------------------
However, when I call : "SELECT seq_min('seq_mytable', 1029);"
I get this other error (translated from french) :
ERROR: «seq_mytable» is not a table
CONTEXT : SQL instruction «LOCK TABLE seq_mytable IN ACCESS EXCLUSIVE
MODE»
PL/pgSQL function "seq_min" line 4 at execute statement
So, it seems that it is impossible to lock a sequence !
If it is the case, how can I achieve the same result without locking the
sequence ?
Thank you again,
Philippe Ferreira.
