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:
Post a Comment