Allow transparently altering View's underlying table

Question

How to create a view that does not prevent altering its underlying table? There's a certain reason why I can't drop and re-create the view every time I need to change a column type or add a new column. This is because the alter script is generated by our ORM when we change the complex type definition and there's a stable deployment procedure I don't want to change.

-- test initialization
drop view if exists test_dep_trk_v;
drop table if exists test_dep_trk;
create table test_dep_trk ( a varchar(10) );

-- the view MUST at all times contain all the columns of the table
create view test_dep_trk_v as select * from test_dep_trk;

-- this statement cannot be changed
alter table test_dep_trk alter column a type varchar(11);

.

ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view test_dep_trk_v depends on column "a"
SQL state: 0A000

Answer 1

Looks like this can be done with the event triggers of 9.4. There are some weak points:

• pg_trigger_depth() always returns zero (Is this a bug?). This is why I had to use a non-temporary table to prevent recursion.
• We need to filter by the altered object name, but there's no way to get it in 9.4. current_query() returns the client's statement even if we're called from a stored procedure.

_

create UNLOGGED table IF NOT EXISTS test_dep_trk_trig_data ( pid integer primary key );

CREATE OR REPLACE FUNCTION test_dep_trk_trig() RETURNS event_trigger AS $$
DECLARE
    v_query text;
BEGIN
  if NOT exists( select * from test_dep_trk_trig_data where pid = pg_backend_pid() ) then
    insert into test_dep_trk_trig_data ( pid ) values ( pg_backend_pid() );
    v_query := lower( current_query() );
    if (v_query like '%test_dep_trk%' and not v_query like '%test_dep_trk_v%') then
      -- RAISE NOTICE '%', v_query;
      drop view if exists test_dep_trk_v;
    end if;
    delete from test_dep_trk_trig_data where pid = pg_backend_pid();
  end if;
EXCEPTION
  WHEN others THEN
    delete from test_dep_trk_trig_data where pid = pg_backend_pid();
    RAISE;
END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION test_dep_trk_trig_end() RETURNS event_trigger AS $$
DECLARE
    v_query text;
BEGIN
  if NOT exists( select * from test_dep_trk_trig_data where pid = pg_backend_pid() ) then
    insert into test_dep_trk_trig_data ( pid ) values ( pg_backend_pid() );
    v_query := lower( current_query() );
    if (v_query like '%test_dep_trk%' and not v_query like '%test_dep_trk_v%') then
      -- RAISE NOTICE '%', v_query;
      create view test_dep_trk_v as select * from test_dep_trk;
    end if;
    delete from test_dep_trk_trig_data where pid = pg_backend_pid();
  end if;
EXCEPTION
  WHEN others THEN
    delete from test_dep_trk_trig_data where pid = pg_backend_pid();
    RAISE;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER test_dep_trk_trig ON ddl_command_start EXECUTE PROCEDURE test_dep_trk_trig();
CREATE EVENT TRIGGER test_dep_trk_trig_end ON ddl_command_end EXECUTE PROCEDURE test_dep_trk_trig_end();