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.

@a_horse_with_no_name, after executing this query, I have to join the results with the other table to create a view — Tridev Chaudhary
– Tridev Chaudhary, Commented Apr 23, 2021 at 6:55
@TridevChaudhary Check my answer. I have also provided a db <> fiddle (DEMO) in my answer. You might need to modify the query according to your table names. — Mushif Ali Nawaz
– Mushif Ali Nawaz, Commented Apr 23, 2021 at 7:40
@a_horse_with_no_name You saved me! I had too many partition tables to delete in one go so combining \gexec followed by the below sql code worked wonders! select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; — Rami
– Rami, Commented Feb 11, 2023 at 19:40

Mushif Ali Nawaz · Accepted Answer · 2021-04-23 07:23:05Z

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

Collectives™ on Stack Overflow

How to execute result of a query in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related