I have a small piece of code to get all sequences and the current and maximum value of all sequences in a database. I have tried
with recursive and some other alternatives through plain sqls, however I couldn't find it. So I decided to use the dynamic sql as below:
do language plpgsql $$ declare qy text; rs record; begin for qy in select 'select current_database(), iss.sequence_schema || ''.'' || ind.sequence_name, concat(''Cur'', ind.lvalue,'',Max:'',iss.maximum_value) from (select sequence_name, last_value lvalue from ' || quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) || ') as ind, information_schema.sequences iss where iss.sequence_name = ind.sequence_name and iss.sequence_schema = ''' || sequence_schema || '''' from information_schema.sequences loop execute qy into rs; raise notice '%', rs; end loop; end $$;
It works well in pgAdmin however, its not working in
psql as expected. I do have two questions here:
Can I return the
resvalues as result in dynamic sql? (I could if I do these operation via a function but I will not be having create / execute function privilege in the database since I will be using a guest user)
RAISE DEBUGnot showing anything in the console (using
psql) even if I set the
notice? I querying a rds instance. Is it something to deal with rds functionality?
Bottom of the line, I need to get all sequence's current value and max value along with schema name. It would be great if anyone shares an another way to achieve this without increasing the sequence value.
(I am using postgres 9.6)