Thread

1. Help with plpgsql - subst variable value

   Alexander Bodnar <bodnar@malva.com.ua> — 2001-02-22T17:48:40Z

   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?
   
   
   
   
   

2. Re: Help with plpgsql - subst variable value

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-02-22T19:17:43Z

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