Help with plpgsql - subst variable value
Hi all.
This my code snapshot:
CREATE FUNCTION CreateObject(varchar(600)) RETURNS int4 AS '
DECLARE
. . .
ObjectSeqName text;
BEGIN
. . ..
ObjectSeqName := ''ObjectLog'' || ObjectID || ''_Seq'';
CREATE SEQUENCE ObjectSeqName start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 CYCLE;
. . .
END IF;
RETURN ObjectID;
END;
' LANGUAGE 'plpgsql';
When I run this function I got
ERROR: parser: parse error at or near "$1"
So how can get value of ObjectSeqName in sequence/table/etc. name?
In 7.1, you should be able to use EXECUTE to execute an sql string.
You can basically build the create sequence string and use
execute to run it. As a stripped down version, I did something like:
CREATE FUNCTION CreateObject(varchar(600)) RETURNS int4 AS '
DECLARE
ObjectSeqName text;
ObjectID ALIAS FOR $1;
BEGIN
ObjectSeqName := ''ObjectLog'' || ObjectID || ''_Seq'';
EXECUTE ''CREATE SEQUENCE '' || ObjectSeqName || '' start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 CYCLE;'';
return 1;
END;
' LANGUAGE 'plpgsql';
When I executed it it created the sequence with the name I passed in.
On Thu, 22 Feb 2001, Alexander Bodnar wrote:
Show quoted text
Hi all.
This my code snapshot:
CREATE FUNCTION CreateObject(varchar(600)) RETURNS int4 AS '
DECLARE
. . .
ObjectSeqName text;
BEGIN
. . ..
ObjectSeqName := ''ObjectLog'' || ObjectID || ''_Seq'';
CREATE SEQUENCE ObjectSeqName start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 CYCLE;
. . .
END IF;
RETURN ObjectID;
END;
' LANGUAGE 'plpgsql';When I run this function I got
ERROR: parser: parse error at or near "$1"So how can get value of ObjectSeqName in sequence/table/etc. name?