From: | Philippe Ferreira <phil(dot)f(at)worldonline(dot)fr> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | My very first PL/pgSQL procedure... |
Date: | 2006-01-25 17:37:21 |
Message-ID: | 43D7B751.2000407@worldonline.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I've got a problem with my very first PL/pgSQL procedure !
I created the following procedure, that should reconfigure a sequence :
---------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION seq_min(sequence_name varchar, minval
integer) RETURNS VOID AS $$
DECLARE
current_seq integer;
BEGIN
LOCK TABLE sequence_name IN ACCESS EXCLUSIVE MODE;
current_seq := last_value FROM sequence_name;
IF current_seq < minval THEN
ALTER SEQUENCE sequence_name RESTART WITH minval;
END IF;
END;
$$ LANGUAGE plpgsql;
---------------------------------------------------------------------------------------------------------------------
I call it from the psql interface by :
SELECT seq_min('seq_mytable', 1029);
But PostgreSQL returns the following error (translated from french) :
ERROR: syntax error on or near «$1» at character 13
QUERY : LOCK TABLE $1 IN ACCESS EXCLUSIVE MODE
CONTEXT : PL/pgSQL function "seq_min" line 4 at SQL statement
LINE 1 : LOCK TABLE $1 IN ACCESS EXCLUSIVE MODE
So it seems that PostgreSQL have troubles handling my variable
"sequence_name"...
Any idea ?
Thank you in advance,
Philippe Ferreira, France.
From | Date | Subject | |
---|---|---|---|
Next Message | andrew | 2006-01-25 17:41:53 | Re: user defined function |
Previous Message | Michael Fuhr | 2006-01-25 17:28:12 | Re: Constraint that compares and limits field values |