Re: My very first PL/pgSQL procedure... - Mailing list pgsql-general

From Jim Buttafuoco
Subject Re: My very first PL/pgSQL procedure...
Date
Msg-id 20060125175736.M14367@contactbda.com
Whole thread Raw
In response to My very first PL/pgSQL procedure...  (Philippe Ferreira <phil.f@worldonline.fr>)
Responses Re: My very first PL/pgSQL procedure...
Re: My very first PL/pgSQL procedure...
List pgsql-general
you need to use EXECUTE to do the dynamic lock table.

sql = 'LOCK TABLE ' || sequence_name  || 'IN ACCESS EXCLUSIVE MODE';
EXECUTE sql;

---------- Original Message -----------
From: Philippe Ferreira <phil.f@worldonline.fr>
To: pgsql-general@postgresql.org
Sent: Wed, 25 Jan 2006 18:37:21 +0100
Subject: [GENERAL] My very first PL/pgSQL procedure...

> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
------- End of Original Message -------


pgsql-general by date:

Previous
From: andrew
Date:
Subject: Re: user defined function
Next
From: "gianni.zammarchi@tiscali.it"
Date:
Subject: xml support on win32 postgres