2

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:

  1. Can I return the res values 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)

  2. Why the RAISE NOTICE or RAISE DEBUG not showing anything in the console (using psql) even if I set the client_min_messages to debug or 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)

Improve this question
2
  • 1
    You could attack the original problem in psql like SELECT format($$SELECT * FROM %I $$, c.oid::regclass) FROM pg_class AS c WHERE relkind = 'S'; and then running \gexec. As for the NOTICE, I don't know, it works as expected locally, and I am not in the position to set up an Aurora cluster to check this.
    – dezso
    Sep 5, 2019 at 15:08
  • @dezso, I could do with help of your example like SELECT format($$SELECT '%I' schemaname, '%I' sequencename, concat(last_value, ',', trim('%I','"')) FROM "%I"."%I" union $$, n.nspname, c.relname, s.maximum_value, n.nspname, c.relname) FROM pg_class c, information_schema.sequences s, pg_namespace n WHERE relkind = 'S' and s.sequence_name = c.relname and n.oid = c.relnamespace and s.sequence_schema = n.nspname I could save this as in plain .sql file and remove the union from last line and execute it. Is there any other feasible way I'm missing here?
    – Spike
    Sep 5, 2019 at 16:28

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.