Re: Help with plpgsql - subst variable value

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:

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