PostgreSQL function to update a serial key column for a variable table

Question

I have a system that syncs various tables between servers at various times. It works fine, except that some tables have SERIAL key columns that get out of date because the sync doesn't update the sequences. So I'm trying to write a pl/pgsql function to make sure that if a table (named in the parameter) has a serial key, its next value is one greater than the greatest value in the table.

Actually, I think I've finally done it, but I figured I'd post the question anyhow, so people can use it or suggest improvements.

Answer 1

You don't really need a function for that. With a variation of this answer this can be done with a single statement:

First we need to find all columns that have use a sequence as a default value:

select table_schema, table_name, column_name,
       pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name)
from information_schema.columns
where table_schema = 'public'
  and column_default like 'nextval%'

Then we can calculate the max value for each of those columns using query_to_xml() and use that result to call setval() for each sequence.

with sequences as (
  select table_schema, table_name, column_name,
         pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name) as col_sequence
  from information_schema.columns
  where table_schema = 'public' --<< adjust for your schemas
    and column_default like 'nextval%'
), maxvals as (
  select table_schema, table_name, column_name, col_sequence,
          (xpath('/row/max/text()',
             query_to_xml(format('select coalesce(max(%I),0) from %I.%I', column_name, table_schema, table_name), true, true, ''))
          )[1]::text::bigint as max_val
  from sequences
  where col_sequence is not null
) 
select table_schema, 
       table_name, 
       column_name, 
       col_sequence,
       max_val,
       setval(col_sequence, max_val)
from maxvals;

Answer 2

This appears to work. It seems one needs to use EXECUTE a lot.

-- Update the serial key sequence of this given table, if appropriate.
CREATE OR REPLACE FUNCTION update_serial(i_table CHARACTER VARYING)
  RETURNS CHARACTER VARYING AS $$
DECLARE
    v_key_col CHARACTER VARYING;
    v_seq_name CHARACTER VARYING;
    v_max RECORD;

BEGIN
    -- Get the name of the primary key, if any.
    SELECT c.column_name, c.data_type INTO v_key_col
    FROM information_schema.table_constraints tc
    JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name)
    JOIN information_schema.columns AS c
        ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
    WHERE constraint_type = 'PRIMARY KEY' and tc.table_name = i_table;

    IF v_key_col IS NULL THEN RETURN 'No key found';
        END IF;

    -- Get the name of the sequence that determines the next number for the primary key, if any.
    SELECT pg_get_serial_sequence(i_table, v_key_col) INTO v_seq_name;

    IF v_seq_name IS NULL THEN RETURN 'No sequence found';
        END IF;

    -- Get the maximum value in the primary key data, and add 1.
    EXECUTE 'SELECT MAX(' || v_key_col || ') + 1 m FROM ' || i_table INTO v_max;

-- Set the value of the sequence, converting to regclass and back to text so as to clean up the name and remove
-- the schema. It needs to put its output somewhere though we're not using it, so it goes back into v_max.
SELECT SETVAL(quote_ident(v_seq_name::regclass::text), v_max.m) INTO v_max;

    RETURN 'Done';
END;
$$ LANGUAGE 'plpgsql';
COMMENT ON FUNCTION update_serial(i_table CHARACTER VARYING) IS
'Update the serial key sequence of this given table, if appropriate.';