Re: My very first PL/pgSQL procedure...

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