Is it possible to write a select * view that includes new columns added to table?

Question

I would like to write a view that is essentially something simple select a + b, * from table_name and will always truly include all columns in table_name, even when I add or remove them.

If I do the following:

create database viewtest;
\c viewtest
create table a (x text);
create view b as select * from a;
alter table a add y text;
select * from b;
 x 
---
(0 rows)

select * from a;
 x | y 
---+---
(0 rows)

\d+ b
                  View "public.b"
 Column | Type | Modifiers | Storage  | Description 
--------+------+-----------+----------+-------------
 x      | text |           | extended | 
View definition:
 SELECT a.x
   FROM a;

I want to essentially create a view where the definition includes * rather than an explicit list of the columns in the table when I created the view. Maybe I'm crazy, but this seems like a practical use case and I can't figure out how to do it for the life of me.

Answer 1

The basic problem for your idea is that a view (or materialized view) uses "early binding", meaning the query is parsed at the time of creation. SELECT * is expanded to the list of columns visible at that time. Postgres registers a row type for the view. Later additions to the table are not cascaded to any views. (And existing columns cannot be deleted due to the registered dependency, unless you delete the view first.)

A function taking a polymorphic input type is a limited workaround, but only for registered row types. Example:

• How to create function(table_name) that returns a set of the table

For your case: SELECT a + b AS ab, * FROM tbl, just use the simple query as given. If a + b is just a placeholder for a more complex calculation, I would suggest to encapsulate the calculation in a function like:

CREATE OR REPLACE FUNCTION f_ab(a int, b int)  -- use actual data types
  RETURNS int  -- use actual data type
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT a + b   -- placeholder for more complex calculation
$func$;

And adapt your query to:

SELECT f_ab(a, b) AS ab, * FROM tbl;