From: | John DeSoi <desoi(at)pgedit(dot)com> |
---|---|
To: | Julio Leyva <jcleyva(at)hotmail(dot)com> |
Cc: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: alter sequence in a function |
Date: | 2007-07-04 12:11:41 |
Message-ID: | FA54F2B7-2F04-4446-80C3-70946D594AD7@pgedit.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Jul 3, 2007, at 6:48 PM, Julio Leyva wrote:
> create or replace function updatesafe() returns integer AS $$
> DECLARE
> maxseq integer;
> alterseq varchar(256);
> thumb integer;
> newvalue integer;
> BEGIN
> newvalue := 10010;
> maxseq := (select max(safeoperationid) from safeopencloseoperation);
>
> if (maxseq < 500) then
> return 3000;
>
> else
> execute 'ALTER sequence safeopencloseoperation_id_seq restart
> with ' || 'newvalue ' ;
> return 10000;
> END IF;
> END;
> $$ language plpgsql
>
> It compiles ok but when I call the function
> it gives me this error
>
> ALTER sequence safeopencloseoperation_id_seq restart with newvalue
> CONTEXT: PL/pgSQL function "updatesafe" line 17 at execute statement
> LINE 1: ...equence safeopencloseoperation_id_seq restart with newvalue
You are appending the literal string "newvalue" not the string
"100010". Change newvalue to text and cast it from an integer, if
necessary. Then you want:
execute 'ALTER sequence safeopencloseoperation_id_seq restart with
' || newvalue ;
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
From | Date | Subject | |
---|---|---|---|
Next Message | Martin Arpon | 2007-07-04 13:14:58 | Restoring a tablespace |
Previous Message | James Wilford | 2007-07-04 08:25:50 | More than one pg_database entry for database |