Re: Learning Plpgsql ?? - Mailing list pgsql-novice

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