| From: | Jonathon Batson <jonathon(at)octahedron(dot)com(dot)au> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Subject: | Learning Plpgsql ?? |
| Date: | 2003-01-23 01:18:49 |
| Message-ID: | 3E2F42F9.2040006@octahedron.com.au |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-novice |
Hi
Newbie to Plpgsql and Postgres.
I am porting dbs from Access97 to postgres, tables and data comes across
fine but the autonumber(sequences) do not
get updated, so I need to do this manually using > SELECT
setval(sequence_name,value);
OK , this is no problem, BUT, I have 90 tables in the db, and around 70
clients to port to postgres.
So a function to do this is needed.
The direction took so far is to create a table, seq_table consisting of
all the sequences information in the db as follows:
sequence_name table_name pk_column
-----------------------------------------------------------------
customer_number_seq customer c_number
purchase_job_number_seq purchase job_number
etc
Then a function that in psuedo code is something like this
for each row in seq_table
get max(pk_column) from table_name
set sequence_name to max
endfor
So my function is:
-- Function: update_seq()
CREATE or REPLACE FUNCTION update_seq() RETURNS text AS '
DECLARE
row RECORD;
maxid INTEGER;
BEGIN
-- select sequence information [ sequence_name, table_name, pk_column]
FOR row IN SELECT * FROM seq_table LOOP
-- get the maxid for row.table_name on row.pkcolumn
SELECT max(row.pk_column) INTO maxid FROM row.table_name;
-- then set the sequence value
SELECT setval(row.sequence_name,maxid);
END LOOP;
RETURN ''done'';
END;
' LANGUAGE 'plpgsql';
The function fails at the line select into line
SELECT max(row.pk_column) INTO maxid FROM row.table_name;
with the following error........something to do with the second var
row.table_name I think.
swimdb=# SELECT update_seq();
NOTICE: Error occurred while executing PL/pgSQL function update_seq
NOTICE: line 14 at select into variables
ERROR: parser: parse error at or near "$2"
Any ideas would be gratefully accepted...
Also a direction on some more detailed PL/pgSQL documentation
Have looked a Postgres Interactive Docs..not so helpfull
Thanks
Jonathon
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andrew McMillan | 2003-01-23 03:22:09 | Re: Learning Plpgsql ?? |
| Previous Message | Rob Klaus | 2003-01-23 01:00:06 | ODBC Documentation |