Обсуждение: Learning Plpgsql ??
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
On Thu, 2003-01-23 at 14:18, Jonathon Batson wrote: > 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'; I think you will need to build the queries as text and use EXECUTE to EXECUTE them: DECLARE dqry TEXT; ... dqry := ''SELECT setval('''' || row.sequence_name || '''', '' || maxid || '');''; EXECUTE dqry; Section 19.5.4 of the docs. Cheers, Andrew. > > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- --------------------------------------------------------------------- Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 Survey for nothing with http://survey.net.nz/ ---------------------------------------------------------------------
I would suggest using the pgAdminII Migration Wizard. It will create all the sequences for you and sets them to the appropriatevalues (max primary key). I just completed a migration from MS SQL Server and it was fantastic. Very, very easy- as long as you don't have binary data, which was an incredible pain. http://pgadmin.postgresql.org/ As far as learning PL/pgSQL goes....I'm having trouble with that too. The examples in the docs aren't very helpful, I'vehad to search the mailing list archives a lot. There should be a well defined example of how to return a ResultSet froma function, for one thing. I found this message in the archives very helpful (and think it should be added to the docs): http://archives.postgresql.org/pgsql-hackers/2002-09/msg01741.php Whereas the docs describe how to do it, but don't show the actual code (in 19.6.1): http://www.postgresql.org/docs/view.php?version=7.3&file=plpgsql-control-structures.html Roman Fail Sr. Web Application Developer POS Portal, Inc. Sacramento, CA USA On Thu, 2003-01-23 at 14:18, Jonathon Batson wrote: > 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'; I think you will need to build the queries as text and use EXECUTE to EXECUTE them: DECLARE dqry TEXT; ... dqry := ''SELECT setval('''' || row.sequence_name || '''', '' || maxid || '');''; EXECUTE dqry; Section 19.5.4 of the docs. Cheers, Andrew. > > 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
Coming from another newbie, here's what I use to set the sequence following an import of text data. I create seqence and table and import one file at a time, for a one time conversion, so I don't need the table of table names,etc., but that's a great idea, like a data dictionary concept. Would this work, modified to fit your loop syntax? select SETVAL('link_rep_link_rep_id_seq', (select max(link_rep_id) from link_rep)) Jim
Hi Jonathan I've been using Postgres for a bit but I'm not really an expert. You have probably seen that the "SERIAL" type expands to 'not null default nextval('"auto_generated_index_name"'::text) If you have imported a large set of records with explicit ids you could simply use the 'setval(index_name,number)' function to reset the sequence's current value. Cheers Rory On 23/01/03, Jonathon Batson (jonathon@octahedron.com.au) wrote: > 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); -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>
Jim Beckstrom <jrbeckstrom@sbcglobal.net> writes: > Coming from another newbie, here's what I use to set the sequence > following an import of text data. I create seqence and table and > import one file at a time, for a one time conversion, so I don't need > the table of table names,etc., but that's a great idea, like a data > dictionary concept. Would this work, modified to fit your loop syntax? > > select SETVAL('link_rep_link_rep_id_seq', (select max(link_rep_id) > from link_rep)) > > Jim For really large tables SELECT SETVAL('link_rep_link_rep_id_seq', (SELECT link_rep_id FROM link_rep ORDER BY link_rep_id DESC LIMIT 1)) will be quite a bit faster (you'll save yourself a tablescan).
Good Idea and it worked, had to play with quotes a bit, duh, Note: the use of quote_literal for seq's and quote_ident for table and column objects. CREATE or REPLACE FUNCTION up_seq() RETURNS text AS ' DECLARE row RECORD; qrystr TEXT; BEGIN -- select sequence information from seq table [ sequence_name, table_name, pk_column] FOR row IN SELECT * FROM swim_seq_temp LOOP qrystr := ''SELECT setval('' || quote_literal(row.sequence_name) || '', ( SELECT max('' || quote_ident(row.pk_column) || '') FROM '' || quote_ident(row.table_name) || ''))''; EXECUTE qrystr; END LOOP; RETURN ''done''; END; ' LANGUAGE 'plpgsql'; Jim Beckstrom wrote: > Coming from another newbie, here's what I use to set the sequence > following an import of text data. I create seqence and table and > import one file at a time, for a one time conversion, so I don't need > the table of table names,etc., but that's a great idea, like a data > dictionary concept. Would this work, modified to fit your loop syntax? > > select SETVAL('link_rep_link_rep_id_seq', (select max(link_rep_id) > from link_rep)) > > Jim > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Just an update on the set sequences for multiple tables across many dbs function. Would fail on empty tables, corrected... Now has an output table that shows sequence value before, after, and record count. Feels more complete now.. -- Function: update_seq() CREATE or REPLACE FUNCTION update_seq() RETURNS text AS ' DECLARE qrystr0 TEXT; qrystr1 TEXT; qrystr2 TEXT; qrystr3 TEXT; qrystr4 TEXT; row RECORD; seq_val RECORD; next_seq RECORD; no_recs RECORD; next_seq_val INTEGER; no_records INTEGER; BEGIN -- output table is seq_values ( seq_name text, old_val int4, new_val int4, record_no int4) qrystr0 := ''TRUNCATE seq_values''; EXECUTE qrystr0; -- select sequence information from seq table -- [ sequence_name, table_name, pk_column] FOR row IN SELECT * FROM swim_sequences LOOP -- retrieve next_seq value, need to use FOR IN EXECUTE LOOP to retrieve data from dynamic queries qrystr1 := ''SELECT nextval('' || quote_literal(row.sequence_name) || '')''; FOR next_seq IN EXECUTE qrystr1 LOOP next_seq_val := next_seq.nextval; END LOOP; -- retrieve record count qrystr2 := ''SELECT count('' || quote_ident(row.pk_column) || '' ) FROM '' || quote_ident(row.table_name); FOR no_recs IN EXECUTE qrystr2 LOOP no_records := no_recs.count; END LOOP; -- set new seq value qrystr3 := ''SELECT setval('' || quote_literal(row.sequence_name) || '', ( SELECT max('' || quote_ident(row.pk_column) || '') FROM '' || quote_ident(row.table_name) || ''))''; FOR seq_val IN EXECUTE qrystr3 LOOP -- covering errors for empty tables IF seq_val.setval IS NULL THEN seq_val.setval := 0; next_seq_val := 0; ELSE -- to give the current seq_val --not next_val next_seq_val := next_seq_val -1; END IF; -- update output table qrystr4 := ''INSERT INTO seq_values ( seq_name, old_val, new_val,record_no ) VALUES ('' || quote_literal(row.sequence_name) || '','' || quote_literal(next_seq_val) || '','' || quote_literal(seq_val.setval) || '','' || quote_literal(no_records) || '')''; EXECUTE qrystr4; END LOOP; END LOOP; RETURN ''done''; END; ' LANGUAGE 'plpgsql'; Output table is........... seq_name | old_val | new_val | record_no ---------------------------------+---------+---------+---------- customer_number_seq | 0 | 5913 | 5913 purchase_job_number_seq | 0 | 8905 | 7644 receipt_id_seq | 0 | 8722 | 8396 account_options_id_seq | 0 | 1 | 1
I took this code from a different message: >CREATE or REPLACE FUNCTION up_seq() RETURNS text AS ' > DECLARE > row RECORD; qrystr TEXT; > BEGIN > -- select sequence information from seq table > [ sequence_name, table_name, pk_column] > FOR row IN SELECT * FROM swim_seq_temp LOOP > > qrystr := > ''SELECT setval('' > || quote_literal(row.sequence_name) > || '', ( SELECT max('' > || quote_ident(row.pk_column) > || '') FROM '' > || quote_ident(row.table_name) > || ''))''; > EXECUTE qrystr; > > END LOOP; > RETURN ''done''; > END; >' LANGUAGE 'plpgsql'; I'm heartened that we have the capabilities to execute dynamic a string like this. However, in SQL Server, this sort of operation slows down the database, because it has to produce a new execution plan. Is the same true for Postgresql? The way SQL Server gets some time back is by the use of sp_executesql. Is there some equivalent in Postgresql? Steve
Taken from Postgres 7.3 Docs
To me this means that 'yes' a new execution plan is produced each time.
19.5.4. Executing dynamic queries
"Unlike all other queries in PL/pgSQL, a query run by an EXECUTE statement is not prepared and saved just once during the life of the server. Instead, the query is prepared each time the statement is run."To me this means that 'yes' a new execution plan is produced each time.