1

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.

  • 1
    There's no way to do this in PostgreSQL. You usually re-create the view after modifying the table. – Craig Ringer Mar 1 '16 at 4:57
  • 1
    @CraigRinger wouldn't it be possible to create a (materialized?) view that uses information taken from information_schema.table and information_schema.columns, generate a list a colums for this particular table and do the proper sum? – KookieMonster Mar 1 '16 at 7:42
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:

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;
  • Just to be clear, it's not possible to use the SELECT you provided at the end in a view and have table changes cascade to the view, right? – swrobel Mar 2 '16 at 19:08
  • @swrobel: Yes, not possible, because * in SELECT * is resolved to the current list of columns when CREATE VIEW ... is executed. – Erwin Brandstetter Mar 3 '16 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.