4

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

psteelk
  • 1,305
  • 3
  • 16
  • 24
  • I don't really understand the question. You want for each id in ids to make a query in a table which name is create by substiting the id ? Or something like that? If it is remotely like this it sounds to me that you need [dynamic sql](http://www.postgresql.org/docs/8.1/static/ecpg-dynamic.html) and/or perhaps temporary table. – tozka Oct 08 '12 at 07:48

2 Answers2

9

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Hi, how to use the result of the query i.e result_id_table (where id is substituted using format or string concat) as a table name ? – psteelk Oct 08 '12 at 09:00
  • 1
    @pravinkenator Aaah, you want dynamic SQL. This is usually a sign of a serious design problem where you should be using an additional column in the target table instead, but occasionally it's OK. If you must do it, the only way to do it is with PL/PgSQL's `EXECUTE` statement. – Craig Ringer Oct 08 '12 at 09:10
  • @pravinkenator Updated with explanation. Try to explain your questions better next time, it really wasn't even remotely clear what you actually wanted. – Craig Ringer Oct 08 '12 at 09:16
  • Thanks ! I got it. Wonderful post ! – psteelk Oct 08 '12 at 09:20
  • @pravinkenator Glad to help. Feel free to +1 if you want :-) and thanks for accepting. It sounds like you might be attempting a multi-tenant database design, so try searching for "postgresql multi-tenant" to get some ideas about how it can be done more easily/safely using schemas and `search_path` or using composite primary keys. – Craig Ringer Oct 08 '12 at 09:44
  • Hi, I'm getting an error if I try to execute something very simple using EXECUTE like this EXECUTE format('SELECT * from result_%s_ta ble', quote_ident((select 70))). Can you help me in this regard ? – psteelk Oct 08 '12 at 10:29
  • @pravinkenator Post a new question with the full query, full text of the error message, Pg version, etc. Feel free to link to it here. – Craig Ringer Oct 08 '12 at 10:39
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.

spen.smith
  • 576
  • 2
  • 16