I have the following query that gets all sequences and their schemas:
SELECT sequence_schema as schema, sequence_name as sequence
FROM information_schema.sequences
WHERE sequence_schema NOT IN ('topology', 'tiger')
ORDER BY 1, 2
I would like to get the current value of each sequence name with something like select last_value from [sequence];. I have tried the following (and a couple variations), but it doesn't work because the syntax isn't correct:
DO $$
BEGIN
EXECUTE
sequence_schema as schema,
sequence_name as sequence,
last_value
FROM information_schema.sequences
LEFT JOIN (
EXECUTE 'SELECT last_value FROM ' || schema || '.' || sequence
) tmp
ORDER BY 1, 2;
END
$$;
I've found some solutions that create functions to execute text or piece together a query inside a function and return the result, but I would prefer to have a single query that I can run and modify however I like.
In Postgres 12, you can use pg_sequences:
select schemaname as schema,
sequencename as sequence,
last_value
from pg_sequences
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다