PostgreSQL How to use the value of one SELECT as the table name of another SELECT

Question

First，I need to query the sequence name

SELECT pg_get_serial_sequence('user', 'user_id')； -- public.user_id_seq

Then，I need to query the last value

SELECT last_value FROM public.user_id_seq ;

How to combine these two queries？

I tried three ways, but none was right

WITH t AS (
    SELECT pg_get_serial_sequence('user', 'user_id')
  )
SELECT * FROM t;

WITH tableName AS ( VALUES ( pg_get_serial_sequence('user', 'user_id') ) ) 
SELECT * FROM tableName

do
$$
declare 
    _query text ;
    _cursor CONSTANT refcursor :='_cursor';
begin
    _query:=format('SELECT * FROM %s ', (SELECT pg_get_serial_sequence('user', 'user_id')));
    open _cursor for execute _query;
end;
$$;
fetch all from _cursor;  -- cursor "_cursor" does not exist

Why do you want to use a cursor? that's just overhead – Frank Heikens Mar 03 '23 at 09:27 — Frank Heikens, Mar 03 '23 at 09:27

score 1 · Accepted Answer · answered Mar 03 '23 at 11:13

No need for PL/pgSQL or dynamic SQL.

You can use query_to_xml() to do this purely in SQL (similar to this answer)

with xml_result as (
  select query_to_xml(format('select last_value from %s', seq_name), true, true, '') as data
  from pg_get_serial_sequence('public.user', 'user_id') as seq_name
)
select (xpath('/row/last_value/text()', data))[1]::text::bigint as last_value
from xml_result;

PostgreSQL How to use the value of one SELECT as the table name of another SELECT

1 Answers1