string substitution with query result postgresql

Question

I have a query which will always return only one element as a result. I want to append the result of this query into a string which I can use for futher processing

Example

select id from ids where some_condition

I want to append this id in a string

like result_(id)_table, where, the id has to be substituted with the id returned from the previous query (Which is essentialy an another table)

and finally, I should be able to execute a query like

select * from result_id_table

Where, "result_id_table" is the name of the table where id is appropriately substituted

Answer 1

I am using Postgres on Redshift, and I ran into an issue of trying to use dynamic dates in etl and found a solution.

One correct way to use a variable's value is with f' ':

query = f''' select * from table where date >= '{my_date}' order by date '''

Disclaimer: this is not a secure solution, but you could alter it to use sqlalchemy text and bindparam--- Docs on sqlalchemy.

Answer 2

Use string concatenation:

-- Demo table structure
CREATE TABLE dummy ( id integer primary key, blah integer not null);
INSERT INTO dummy(id, blah) VALUES (1,1);

-- Single-valued query
SELECT id FROM dummy WHERE blah = 1;

-- Formatted with string concatenation
SELECT 'result_'||(id::text)||'_table' FROM dummy WHERE blah = 1;

-- Formatted using the `format` function
SELECT format('result_%s_table', id) FROM dummy WHERE blah = 1;

Combine into string using subquery if you're collecting other information

SELECT 'result_'||(SELECT id FROM dummy WHERE blah = 1)||'_table'
FROM .... WHERE ...

or use a join.

Your edit suggests you want to then use that as a table name. This probably means your design is bad. Instead of:

CREATE TABLE sometable_1 ( id integer primary key, ...);
CREATE TABLE sometable_2 ( id integer primary key, ...);
CREATE TABLE sometable_3 ( id integer primary key, ...);
...
CREATE TABLE sometable_n ( id integer primary key, ...);

you're almost always better off with:

CREATE TABLE sometable(
     id integer not null,
     discriminator integer not null,
     primary key (id, discriminator),
     ...
);

or per-schema tables. If for some reason you're stuck with this approach you can use PL/PgSQL's EXECUTE statement to run dynamic SQL, like:

EXECUTE format('SELECT * FROM sometable_%s WHERE blah = ?', 
            quote_ident((SELECT id FROM dummy WHERE blah = 1))
        )
USING 2;

to query "sometable_1" for rows where "blah = 2". More info in the manual, see EXECUTE ... USING.

It is simply not possible to do this in regular SQL for PostgreSQL. Do it in the application, or in PL/PgSQL. A PL/PgSQL DO block can be used, but performance will be awful if you're relying on that for everything.

Fix your design now, before it's too late.