Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE static (pool int, spa int, house_size int, sauna int);
INSERT INTO static VALUES
  (1, 1, 25, 1)
, (1, 0, 35, 1)
;

CREATE TABLE static2 (pool int, spa int, house_size int, sauna int, no_rooms int);
INSERT INTO static2 VALUES
  (1, 1, 25, 1, 2)
, (1, 0, 35, 1, 3)
;



CREATE TABLE



INSERT 0 2



CREATE TABLE



INSERT 0 2


CREATE OR REPLACE FUNCTION f_dynamic_select(_tbl regclass
                                                  , _cols VARIADIC text[])
-- RETURNS text  -- to debug
  RETURNS SETOF record
  LANGUAGE plpgsql AS
$func$
BEGIN
-- RETURN  -- to debug
   RETURN QUERY EXECUTE
   format(
      'SELECT %s FROM %s'
    , ( --  SELECT list
      SELECT string_agg(COALESCE(quote_ident(a.attname)
                              , '0 AS ' || quote_ident(t.col)  -- type integer!
                                ), ', ' ORDER  BY t.ord)
      FROM   unnest(_cols) WITH ORDINALITY t(col, ord)
      LEFT   JOIN pg_attribute a ON a.attrelid = _tbl
                                AND a.attnum > 0
                                AND NOT a.attisdropped 
                                AND a.attname = t.col
      )
    , _tbl
   );
END
$func$;


CREATE FUNCTION


-- call with static (missing no_rooms)
SELECT * FROM f_dynamic_select('static', 'pool', 'spa', 'sauna', 'house_size', 'no_rooms')
AS t(pool int, spa int, house_size int, sauna int, no_rooms int);


pool
spa
house_size
sauna
no_rooms


1
1
1
25
0

1
0
1
35
0



SELECT 2


-- call with static2 (existing no_rooms)
SELECT * FROM f_dynamic_select('static2', 'pool', 'spa', 'sauna', 'house_size', 'no_rooms')
AS t(pool int, spa int, house_size int, sauna int, no_rooms int);


pool
spa
house_size
sauna
no_rooms


1
1
1
25
2

1
0
1
35
3



SELECT 2


-- your example call with expressions top of columns
SELECT pool, case when spa = 1 then 1 else 0 end as has_spa
     , sauna, house_size
     , case when no_rooms > 2 then 1 else 0 end as rooms 
FROM f_dynamic_select('static', 'pool', 'spa', 'sauna', 'house_size', 'no_rooms')
AS t(pool int, spa int, house_size int, sauna int, no_rooms int);



pool
has_spa
sauna
house_size
rooms


1
1
25
1
0

1
0
35
1
0



SELECT 2