Thread: pl/pgSQL sequence question

Hi,

I'm attempting to build a table within a pl/pgsql function that is to be populated so that it has an index column
1...N. (without looping).  I used to do this in sybase and found it extremely useful) 
So to try and acheive this I:
A, Create a sequence for the identiy column, then place this as the default value in the table.
B, Use a large table and a limit on the select to populate with the correct number of rows.
The problem is that the table does not seem to see the sequence and the function fails:-

CREATE or REPLACE FUNCTION debugMe(integer,resolution) returns integer AS '
   DECLARE
      size alias for $1;
      resolution alias for $2;

      createseq varchar(200);
      createtable varchar(200);
      dropseq varchar(200);
      droptable varchar(200);

   BEGIN
      --sequence commands
      createseq := ''CREATE SEQUENCE 'tmp_seq INCREMENT ''||resolution|| '' MINVALUE 1 START 1'';      dropseq   :=
''DROPSEQUENCE ''||seqname; 

      --table commands
      createtable := ''CREATE TABLE debugtab(idcol INTEGER PRIMARY KEY DEFAULT NEXTVAL(tmp_seq), value int )'';
      droptable := ''DROP TABLE ''||tablename;
      --insert data commands
      loadtable := ''insert into debugtab(value) select 0 from largetable where positivenumber > 0 limit ''||size;

      --A
      execute createseq;
      --B
      execute createtable;
      --why can it not find tmp_seq??

      --C insert data, not a worry at the moment
     execute loadtable;

      --D do the major processing

      --Z clean up
      execute dropseq;
      execute droptable;

   END
' LANGUAGE 'plpgsql';

error message:-
NOTICE:  line ?? at execute statementERROR:  Attribute 'tmp_seq' not found
Hope someone can help
Many thanks,
Steve

"Stephen Shorrock" <smsh@bas.ac.uk> writes:
>       createtable := ''CREATE TABLE debugtab(idcol INTEGER PRIMARY KEY DEFAULT NEXTVAL(tmp_seq), value int )'';

>       --why can it not find tmp_seq??

You need quotes.  For mostly historic reasons, nextval takes a string
argument containing the name of the sequence, which is not what you
wrote here.

You could avoid messing directly with the sequence if you defined the
column as "idcol SERIAL PRIMARY KEY", instead.

            regards, tom lane

"Stephen Shorrock" <smsh@bas.ac.uk> writes:
> Thanks for your response Tom,
> I thought that you might like to know I've implemented you idea of using idcol SERIAL PRIMARY KEY, which is a really
goodtip. Although not in a function as I am unable to remove this durring the function call:  
> ERROR:  RelationForgetRelation: relation 47527448 is still open

I think this could only happen in pre-7.3 Postgres.  Consider updating.

            regards, tom lane

Thanks for your response Tom,

I thought that you might like to know I've implemented you idea of using idcol SERIAL PRIMARY KEY, which is a really
goodtip. Although not in a function as I am unable to remove this durring the function call:  
ERROR:  RelationForgetRelation: relation 47527448 is still open
I can comment out the dropsequence command but this will leave a sequence debugtab_idcol_seq in the data base and I am
unableto run the procedure again before deleting this, so I placed the drop call at the start of the function.  It
thereforelooks like you can not create then drop a sequence in a function although you can drop then create one!? 

the offending code:-

CREATE or REPLACE FUNCTION debugMe(integer,integer) returns integer AS '   DECLARE      size alias for $1;
resolutionalias for $2;      createtable varchar(200);      dropseq varchar(200);      droptable varchar(200);
loadtablevarchar(200);   BEGIN      --sequence commands 
      dropseq := ''DROP SEQUENCE debugtab_idcol_seq'';
--executing the dropseq command here works?! (that's if the function has already beeen run!!)
      --table commands      createtable := ''CREATE TABLE debugtab(idcol SERIAL PRIMARY KEY, value int )'';
      droptable := ''DROP TABLE debugtab'';
      --insert data commands      loadtable := ''insert into debugtab(value) select 0 from data_flt where dfl_value > 0
limit''||size;      --A      --execute createseq;      --B      execute createtable;      --why can it not find
tmp_seq??     --C insert data, not a worry at the moment     execute loadtable;            --D do the major processing
         --Z clean up      execute droptable;      execute dropseq; --does not work here    return 0;   END;' LANGUAGE
'plpgsql';

>>> Tom Lane <tgl@sss.pgh.pa.us> 12/22/03 10:08PM >>>
"Stephen Shorrock" <smsh@bas.ac.uk> writes:
>       createtable := ''CREATE TABLE debugtab(idcol INTEGER PRIMARY KEY DEFAULT NEXTVAL(tmp_seq), value int )'';

>       --why can it not find tmp_seq??

You need quotes.  For mostly historic reasons, nextval takes a string
argument containing the name of the sequence, which is not what you
wrote here.

You could avoid messing directly with the sequence if you defined the
column as "idcol SERIAL PRIMARY KEY", instead.

            regards, tom lane