2 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

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:

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 date)  -- use actual data types
  RETURNS int AS   -- use actual data type
$func$
SELECT a + b  -- placeholder for more complex calculation
$func$  LANGUAGE sql STABLE;

And adapt your query to:

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

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:

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 date)  -- use actual data types
  RETURNS int AS   -- use actual data type
$func$
SELECT a + b  -- placeholder for more complex calculation
$func$  LANGUAGE sql STABLE;

And adapt your query to:

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

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:

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 date)  -- use actual data types
  RETURNS int AS   -- use actual data type
$func$
SELECT a + b  -- placeholder for more complex calculation
$func$  LANGUAGE sql STABLE;

And adapt your query to:

SELECT f_ab(a, b) AS ab, * FROM tbl;
1
source | link

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:

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 date)  -- use actual data types
  RETURNS int AS   -- use actual data type
$func$
SELECT a + b  -- placeholder for more complex calculation
$func$  LANGUAGE sql STABLE;

And adapt your query to:

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