How to automatically rename column names in SELECT a.*, b.* to have ie. prefixes a_ and b_ in result column names?

Question

I have two tables (a,b), or joining table to itself, having the same column names. (The table(s) is wide, having about hundred columns.)

Using:

SELECT a.*, b.*
FROM a LEFT JOIN b ON b.id=a.id AND b.date1=a.date2;

... works, but now if I want to make a view from it:

CREATE OR REPLACE VIEW v_test_ab AS
SELECT a.*, b.*
FROM a LEFT JOIN b ON b.id=a.id AND b.date1=a.date2;

... it cannot make a view, because there would be multiple columns with same name.

In PostgreSQL, is there any way to automatically rename the column names of a result table using given pattern?

Example something like:

CREATE OR REPLACE VIEW v_test_ab AS
SELECT a.* AS a_*, b.* AS b_*
FROM a LEFT JOIN b ON b.id=a.id AND b.date1=a.date2;

Or perhaps something like:

SELECT a.* AS generate_column_names('a_%', a.*)
      , b.* AS generate_column_names('b_%', b.*)

Avoiding to need to write:

CREATE OR REPLACE VIEW v_test_ab AS
SELECT 
    a.id as a_id, a.date1 as a_date1, a.date2 as a_date2 -- and so on 
    , b.id as b_id, b.date1 as b_date1, b.date2 as b_date2 -- and so on
FROM a LEFT JOIN b ON b.id=a.id AND b.date1=a.date2;

I can of course use pattern matching search & replace in IDE-editor, but an end result looks like bloated. (The real world case I have, has four wide source tables with many same column names.)

Answer 1

While it is possible to wrap the creation query in an execute statement, joining the catalog to fetch the table and column names, it will be difficult to read.

Instead, the most simple and fast way would be to automatically build the list of column and to copy - paste it in your query.

As a bonus, the day someone asks to rename/alter/drop a specific column, there is a written record of its usage.

SELECT table_name || '.' || column_name || ' as a_' || column_name || ', ' 
FROM information_schema.columns 
WHERE table_schema = 'my_schema' 
  AND table_name= 'my_tablename';

         ?column?
-------------------------
 my_tablename.id as a_id,
 my_tablename.title as a_title,
 my_tablename.a as a_a,