Thread: 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.