How to use selected value as table name for another select in PostgreSQL

user_16544035 0
Viewed 369

I have table with two coloms source_name and output_name:

CREATE TABLE all_table(
  source_name text,
  target_name text
);

Source name it is some external data name. Target name it is auto-generated table name in my DB. There is a relationship between the source name and the target name, there is only one target name for each source name. I have additional table in DB:

CREATE TABLE output_table_1(
  first_name text,
  second_name text,
  birthday timestamp
);

CREATE TABLE output_table_2(
  login text,
  money int
);

In table "all_table" I have some rows:

|   source_name |    target_name |
|---------------|----------------|
| personal data | output_table_1 |
|    login data | output_table_2 |

I want select information from correct table by source name. So I tried

WITH selected_table AS (
  SELECT target_name FROM all_table WHERE source_name='personal data'
)
SELECT * FROM selected_table;

And also

SELECT first_name FROM 
(SELECT target_name FROM all_table WHERE source_name='personal data') AS out_table;

But Postgres print me only correct target name

|    target_name |
|----------------|
| output_table_1 |

I want to get something similar on my query

| first_name | second_name | birthday |
|------------|-------------|----------|
|     FName1 |      SName1 |    Date1 |
|     FName2 |      SName2 |    Date2 |
|     FName3 |      SName3 |    Date3 |
|     FName4 |      SName4 |    Date4 |
|        ... |         ... |      ... |

I've also tried this query

DO
$$
BEGIN
  EXECUTE format('SELECT * FROM %s LIMIT 10', (SELECT target_name FROM all_table WHERE source_name='personal data'));
END;
$$ LANGUAGE plpgsql;

Query executed but nothing happened. Surfing on Google doesn't do anything useful. But mb I'm bad in this.

¯\(ツ)/¯

postgresql sql
1 Answers

if you want to obtain data from DO block you need to define cursor for query.

do
$$
declare 
    _query text ;
    _cursor CONSTANT refcursor :='_cursor';
begin
    _query:='Select * from '||(Select tab_name from ... where ..);
    open _cursor for execute _query;
end;
$$;
fetch all from _cursor;
user_133296050
Related