重建Database之後，要如何reset postgresql 的serial sequence以符合目前資料的max pkey

1.建立function dbaegis_reset_table_seq_f

2.執行sql: select dbaegis_reset_table_seq_f('customers');

CREATE OR REPLACE FUNCTION dbaegis_reset_table_seq_f(p_table_name varchar)
  RETURNS bigint
  LANGUAGE plpgsql
AS
$body$
DECLARE

    /* 
      Description:  reset the table sequence with current id
      Parameter:    p_table_name: table name the contain sequence
      Return:     the max sequence that has been set
      Exception:    when others return error message
      Exampe:     select dbaegis_reset_table_seq_f('al_rpt_history');
      Creation:   2011-03-03 by Marc Hsiao
    */


   ls_current_statement      VARCHAR(4096) default '';
   v_sql_stmt varchar default '';
   r1 record;
   r2 record;
   v_max_seq bigint default 0;



BEGIN
  ls_current_statement = 'Open first cursor';
  for r1 in (SELECT 'select (coalesce(max(' ||column_name|| '),0) +1)::bigint as max_seq from ' ||table_name|| ' where ' ||column_name|| ' > 1' as max_seq_sql,
           'alter sequence ' ||pg_get_serial_sequence(table_name,column_name) || ' restart with ' AS alter_sql
    FROM information_schema.columns
    WHERE 1 = 1
    and (table_name = p_table_name or p_table_name='')
    AND   column_default LIKE 'nextval%') loop
  ls_current_statement = 'Open second cursor';
  for r2 in execute r1.max_seq_sql loop
    ls_current_statement = 'assign max_seq';
    v_max_seq = r2.max_seq;
    ls_current_statement = 'Concate alter statement';
    v_sql_stmt = r1.alter_sql||r2.max_seq;
    raise notice '%', v_sql_stmt;
    ls_current_statement = 'current STMT << '||v_sql_stmt||' >> ';
    ls_current_statement = 'Execute alter statement';
    execute v_sql_stmt ;
  end loop;
end loop;
return v_max_seq;
   EXCEPTION
     WHEN OTHERS THEN
          RAISE EXCEPTION '<<dbaegis_reset_table_seq_f error: table:%, failed to execute: % >>', p_table_name, ls_current_statement || ' ErrCode: ' || SQLSTATE || '-' || SQLERRM;
   END;
$body$
 COST 100
;