Tuesday, September 25, 2012

create a sequence that start with max id of another table in PostgreSQL


recently I had a chance to work around getting value from different table and merge into single table as part of the migration.

during the same I have to create a sequence that starts with maximum id of another table and increment by,

any way that can be created by simple as
 

SELECT max(col_name) from table_name  -- it 'll rtn max id i.e 89
CREATE SEQUENCE seqtest INCREMENT BY 1 STARTS WITH 89;
the same can be written in function, to shun the half human interaction.

the function below will find the max id from given table and assign it into sequence (Assign max(id) to Sequence)

the function find_max()  would return the max id  i.e currval of the sequence.

create or replace function find_max_seq(tablename varchar, colname varchar) 
returns int 
language 'plpgsql'
as 
$$
declare 
 maxno integer;
begin 
 execute 'select  max('||colname|| ')  from ' || tablename  into  maxno ;   
 --execute 'select max(cast( '||colname|| ' as int))  from ' || tablename  into  maxno ;   
 raise info  ' the val : % ', maxno;
 execute 'create sequence seqtest increment by 1 start with  ' || maxno; 
return maxno;
end;
$$
  
 SELECT  findmax('masters.m_approve_status', 'status_id')

No comments: