modifying views

From: "Sim Zacks" <sim(at)nospam(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: modifying views
Date: 2005-02-02 09:16:56
Message-ID: ctq5tc$g0u$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I read the following thread from Nov 2002 on the impossibilities of
modifying a view and I was wondering if anything had changed in this regard
since then?
http://archives.postgresql.org/pgsql-hackers/2002-11/msg00609.php

Basically I want to remove a column from a table. The column is used in a
view. The view, but not the column that I want to remove, is used in 24
other views. A number of those views are also used in other views...

I can't remove the columns from the view without dropping over 100 other
views, removing the columns from this view and recreating them. I was able
to remove the columns from the table by modifying the view so instead of
"table1.field1" it has "null::varchar as field1"
The problem is that the field is still in the view and I don't want it there
(obviously).

I read somewhere that a possible solution would be to do a pg_dump, manually
change the text file and then do a pg_restore. Unfortunately, that means
taking the system offline, which I can't do.

Even adding a column to a view isn't allowed. So if I want a new field in my
table to be accessible in the same manner that the view would be accessible
in, I would have to go through the same process of dropping 100 views,
making my change and then recreating 100 views. Talk about inefficient.

Are there any realistic methods for changing the number of columns in a
view? Is it possible to manually modify the backend? I looked at the
pg_views view SQL and it seemed to work completely internally.
the definition is called by this function "pg_get_viewdef(c.oid) AS
definition"
and the pg_get_viewdef(oid) function just calls 'pg_get_viewdef' with a
language Internal (as shown below)

CREATE OR REPLACE FUNCTION pg_get_viewdef(oid)
RETURNS text AS
'pg_get_viewdef'
LANGUAGE 'internal' STABLE STRICT;
GRANT EXECUTE ON FUNCTION pg_get_viewdef(oid) TO public;
COMMENT ON FUNCTION pg_get_viewdef(oid) IS 'select statement of a view';

I could use any help that you can give me.
Thanks
Sim

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2005-02-02 09:31:20 Re: Weird PostgreSQL crashes on FC2/FC3 64-bit
Previous Message Burak B&B 2005-02-02 08:30:06 delphi -> postgresql database connect ???