Is there any way to replace the table name in a query with value stored in another table ? This is in postgres sql
Eg
Meta_table
col1 | col 2
Table 1 | val1
Table 2 | val2
My requirement
select *
from (select col1 from meta_table where col2 = val2)
Probably the most flexible and efficient way is dynamically creating a temporary view using function:
create or replace function f_prepare(tname text, vname text) returns text language plpgsql as $$
begin
execute format(
'create or replace temporary view %I as select * from %I',
vname, tname);
return vname;
end $$;
Then you can use the created view in usual way, for example;
select f_prepare('pg_class', 'v_class');
select * from v_class where relname = 'pg_database'; -- Index on the source table will be used here
and using your code:
select f_prepare((select col1 from meta_table where col2 = 'val2'), 'v');
select * from v;
And as any other temporary objects, created views will not conflict with other sessions and will be dropped on disconnect.
If you want to change the table name of a table, then you can just update the relname column in table pg_class. But for this, you need admin access to the Postgresql.
The query goes like:-
update pg_class set relname='new_table_name' where relname='old_table_name';
So to do this in single line, You can do like this:
update pg_class set relname=(select col1 from meta_table where col2 = val2) where relname='old_table_name';
You can use Do statement with cursor:
Try This:
DO $$
DECLARE
_query text;
_cursor CONSTANT refcursor := '_cursor';
BEGIN
_query := 'SELECT * FROM '|| (select col1 from meta_table where col2 = 'val1');
OPEN _cursor FOR EXECUTE _query;
END
$$;
FETCH ALL FROM _cursor;
