Re: pl/pgSQL sequence question

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 good tip. 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 unable to run the procedure again before deleting this, so I placed the drop call at the start of the function. It therefore looks 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; resolution alias for $2; createtable varchar(200); dropseq varchar(200); droptable varchar(200); loadtable varchar(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(at)sss(dot)pgh(dot)pa(dot)us> 12/22/03 10:08PM >>>
"Stephen Shorrock" <smsh(at)bas(dot)ac(dot)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