Re: Learning Plpgsql ??

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