Arbitrary string names for Postgres columns

Question

Problem definition:

• I have a Postgres table A holding an arbitrary amount of different columns with different types, a single serial PK and millions of rows. Example:

id | val1 | val2  | val3
---+------+-------+-----
1  | 45.2 | 52.6  | 222
2  | 5.32 | 12.34 | 193

• I have another table B, mapping table A column names to free text string names (unique).

value_name | col_name
-----------+---------
 "Value 1" | "val1"
 "Value 2" | "val2"
 "Value 3" | "val3"

• I want to run select queries on table A to retrieve data by free text names (instead of column names). A single query should return the data.

Something like this would be great:

SELECT id
  , alias('Value 2', 'A')
  , alias('Value 3', 'A')
from  A

or

SELECT *
from alias('Value 2', 'Value 3', 'A')

would return

id | val2  | val3
---+-------+-----
1  | 52.6  | 222
2  | 12.34 | 193

(I don't mind returning id always without explicitly asking for it, but I do need an ability to query the arbitrary amount of columns and return their original column names)

Solution flexibility

• The solution can include Postgres functions, rules, extensions, triggers - anything which does not change the basic structure of table A.
• The solution can suggest any structure for table B.
• Postgres version 11

Tnx!!

Answer 1

You can create a view like this:

CREATE VIEW A_with_col_names AS select val1 "Value 1", val2 "Value 2" ...  from A;

Then the view can be used like:

Select * from A_with_col_names where "Value 1" = 'xyz'

Answer 2

I can see a solution. It is not perfect though.

The principle is to build a temp table dynamically, filled it after that.

So for the following test base :

create table test
(
    id int primary key
    , val1 float
    , val2 float
    , val3 float
);

insert into test values
(1, 45.2, 52.6, 222), (2,5.32,12.34,193);

create table test_2
(
    value_name varchar
    , col_name varchar
);

insert into test_2 values 
('Value 1', 'val1')
, ('Value 2', 'val2')
, ('Value 3', 'val3') ;

you can implement the following procedure :

create or replace procedure querier (in_params varchar[])
language plpgsql 
as $$
declare
    query varchar;
    column_names varchar;
begin
    -- you can do pretty much all you want here to build your columns
    -- for example add a column 'type' to your second table
    select 
        string_agg(col_name, ', ') into column_names
    from test_2
    where value_name in (select unnest(in_params));

    drop table if exists temp_test cascade;
    query := 'CREATE TEMP TABLE temp_test AS 
    select id, '||column_names||' 
    from test;';
    execute query;
    --raise notice '%', query;
end;
$$;

And finally call the procedure and select the whole temp table:

call querier(cast('{"Value 1", "Value 3"}' as varchar[]));
select * from temp_test;