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