How to execute result of a query in PostgreSQL

Question

I am trying to create a view by joining two tables. These two tables have a few columns with the same name. Which gives an error

SQL Error [42701]: ERROR: column "column_name" specified more than once

I cannot use column names while creating the view as there are 30+ columns and new columns will be added to both tables over the period of time. Hence, I have to use * to get all the columns.

Now, to eliminate columns which exist in both the table, I went ahead and did this:

SELECT 'SELECT ' || STRING_AGG('u2.' || column_name, ', ') || ' FROM schema_name.table_name u2' 
FROM information_schema.columns 
WHERE table_name = 'table_name' 
  AND table_schema = 'schema_name' 
  AND column_name NOT IN ('column_name');

This gives me the query to select data from schema_name.table_name without the column column_name. Great!!

The problem: How do I execute the result of the above query?

I tried PREPARE statement, it is just executing the above query and not the result of the above query.

Also, creating a temporary table with no column "column_name" isn't a viable solution.

Answer 1

You need to prepare a dynamic query and then EXECUTE it. It would be something like this:

DO
$do$
    BEGIN

        EXECUTE (
            SELECT CONCAT('CREATE VIEW temp_view AS SELECT ',
                          -- SELECT table1 columns here
                          (SELECT STRING_AGG('u1.' || column_name, ', ')
                           FROM information_schema.columns
                           WHERE table_name = 'table1'
                             AND table_schema = 'schema_name'
                          -- AND column_name NOT IN ('column_name')  -- not omitting for table1
                          ),
                          ', ',
                           -- SELECT table2 columns here
                          (SELECT STRING_AGG('u2.' || column_name, ', ')
                           FROM information_schema.columns
                           WHERE table_name = 'table2'
                             AND table_schema = 'schema_name'
                             AND column_name NOT IN ('column_name')),
                           -- Dynamically prepare the FROM and JOIN clauses
                          ' FROM table1 u1 JOIN table2 u2 ON u1.id = u2.table1_id'));

    END
$do$;

CHECK DEMO