Learning Plpgsql ??
От | Jonathon Batson |
---|---|
Тема | Learning Plpgsql ?? |
Дата | |
Msg-id | 3E2F42F9.2040006@octahedron.com.au обсуждение исходный текст |
Ответы |
Re: Learning Plpgsql ??
Re: Learning Plpgsql ?? |
Список | 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
В списке pgsql-novice по дате отправления: