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
;
