pg_views.definition

Christopher Kings-Lynne wrote:

Hi,

Would it be possible to add a new attribute to pg_views that stores the
original view definition, as entered via SQL?

This would make the lives of those of us who make admin interfaces a lot
easier...

We actually reverse it on the fly:

test=> \d xx
View "xx"
Column | Type | Modifiers
---------+------+-----------
relname | name |
View definition: SELECT pg_class.relname FROM pg_class;

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

We actually reverse it on the fly:

test=> \d xx
View "xx"
Column | Type | Modifiers
---------+------+-----------
relname | name |
View definition: SELECT pg_class.relname FROM pg_class;

Well, no - that's just dumping out the parsed form.

eg.

test=# create view v as select 1 in (1,2,3,4);
CREATE
test=# select * from v;
?column?
----------
t
(1 row)

test=# \d v
View "v"
Column | Type | Modifiers
----------+---------+-----------
?column? | boolean |
View definition: SELECT ((((1 = 1) OR (1 = 2)) OR (1 = 3)) OR (1 = 4));

It's really annoying when people save their view definition in phpPgAdmin
and when they load it up again it's lost all formatting. Functions and
rules, for instance keep the original formatting somewhere.

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

It's really annoying when people save their view definition in phpPgAdmin
and when they load it up again it's lost all formatting. Functions and
rules, for instance keep the original formatting somewhere.

Rules do not. (A view is just a rule anyway.)

Functions do, but that's because their definition is entered as a text
string, which leads directly to those quoting headaches that you're
all too familiar with.

I've thought occasionally about improving the lexer so that parsetree
nodes could be tagged with the section of the source text they were
built from (probably in the form of a (start offset, end offset) pair).
This was mainly for use in improving error reporting in the
parse-analysis phase, but it might be useful for storing original source
text for rules too.

regards, tom lane

Bruce Momjian wrote:

Christopher Kings-Lynne wrote:

Hi,

Would it be possible to add a new attribute to pg_views that stores the
original view definition, as entered via SQL?

This would make the lives of those of us who make admin interfaces a lot
easier...

We actually reverse it on the fly:

We do, but as soon as you break the view by dropping an underlying
object it fails to reconstruct. So having the original view definition
at hand could be useful for some ALTER VIEW RECOMPILE command.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Jan Wieck <JanWieck@Yahoo.com> writes:

We actually reverse it on the fly:

We do, but as soon as you break the view by dropping an underlying
object it fails to reconstruct. So having the original view definition
at hand could be useful for some ALTER VIEW RECOMPILE command.

Note that the assumptions underlying this discussion have changed in
CVS tip: you can't break a view by dropping underlying objects.

regression=# create table foo(f1 int, f2 text);
CREATE TABLE
regression=# create view bar as select * from foo;
CREATE VIEW
regression=# drop table foo;
NOTICE: rule _RETURN on view bar depends on table foo
NOTICE: view bar depends on rule _RETURN on view bar
ERROR: Cannot drop table foo because other objects depend on it
Use DROP ... CASCADE to drop the dependent objects too

or

regression=# drop table foo cascade;
NOTICE: Drop cascades to rule _RETURN on view bar
NOTICE: Drop cascades to view bar
DROP TABLE
-- bar is now gone

Auto reconstruction of a view based on its original textual definition
is still potentially interesting, but I submit that it won't necessarily
always give the right answer.

regards, tom lane

Tom Lane wrote:

Auto reconstruction of a view based on its original textual definition
is still potentially interesting, but I submit that it won't necessarily
always give the right answer.

Sure, it's another bullet to shoot yourself into someone elses foot.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Jan Wieck wrote:

Tom Lane wrote:

Auto reconstruction of a view based on its original textual definition
is still potentially interesting, but I submit that it won't necessarily
always give the right answer.

Sure, it's another bullet to shoot yourself into someone elses foot.

Do we want this on TODO?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

We do, but as soon as you break the view by dropping an underlying
object it fails to reconstruct. So having the original view definition
at hand could be useful for some ALTER VIEW RECOMPILE command.

Note that the assumptions underlying this discussion have changed in
CVS tip: you can't break a view by dropping underlying objects.

regression=# create table foo(f1 int, f2 text);
CREATE TABLE
regression=# create view bar as select * from foo;
CREATE VIEW
regression=# drop table foo;
NOTICE: rule _RETURN on view bar depends on table foo
NOTICE: view bar depends on rule _RETURN on view bar
ERROR: Cannot drop table foo because other objects depend on it
Use DROP ... CASCADE to drop the dependent objects too

Hrm - looks like we really need CREATE OR REPLACE VIEW...

Chris

On Wed, 17 Jul 2002, Christopher Kings-Lynne wrote:

We do, but as soon as you break the view by dropping an underlying
object it fails to reconstruct. So having the original view definition
at hand could be useful for some ALTER VIEW RECOMPILE command.

Note that the assumptions underlying this discussion have changed in
CVS tip: you can't break a view by dropping underlying objects.

regression=# create table foo(f1 int, f2 text);
CREATE TABLE
regression=# create view bar as select * from foo;
CREATE VIEW
regression=# drop table foo;
NOTICE: rule _RETURN on view bar depends on table foo
NOTICE: view bar depends on rule _RETURN on view bar
ERROR: Cannot drop table foo because other objects depend on it
Use DROP ... CASCADE to drop the dependent objects too

Hrm - looks like we really need CREATE OR REPLACE VIEW...

I have written a patch for this. It is in an old source tree. I intend on
getting it together by august, along with create or replace trigger.

Gavin

Hrm - looks like we really need CREATE OR REPLACE VIEW...

I have written a patch for this. It is in an old source tree. I intend on
getting it together by august, along with create or replace trigger.

Sweet. I was going to email to see if you had a copy of your old create or
replace function patch that I could convert. (Just as soon as this drop
column stuff is done.)

Chris

Christopher Kings-Lynne wrote:

We do, but as soon as you break the view by dropping an underlying
object it fails to reconstruct. So having the original view definition
at hand could be useful for some ALTER VIEW RECOMPILE command.

Note that the assumptions underlying this discussion have changed in
CVS tip: you can't break a view by dropping underlying objects.

regression=# create table foo(f1 int, f2 text);
CREATE TABLE
regression=# create view bar as select * from foo;
CREATE VIEW
regression=# drop table foo;
NOTICE: rule _RETURN on view bar depends on table foo
NOTICE: view bar depends on rule _RETURN on view bar
ERROR: Cannot drop table foo because other objects depend on it
Use DROP ... CASCADE to drop the dependent objects too

Hrm - looks like we really need CREATE OR REPLACE VIEW...

The problem is that you would still need to keep a copy of your view
around to recreate it if you wanted to drop and recreate a table it
depends on. I really like the idea about keeping the original view
source handy in the system catalogs.

It is common in Oracle to have dependent objects like views and packages
get invalidated when something they depend on is dropped/recreated.
Would it make sense to do something like that? I.e. set a relisvalid
flag to false, and generate an ERROR telling you to recompile the object
if you try to use it while invalid.

Joe

Joe Conway wrote:

The problem is that you would still need to keep a copy of your view
around to recreate it if you wanted to drop and recreate a table it
depends on. I really like the idea about keeping the original view
source handy in the system catalogs.

This has been the case all the time. I only see an attempt to
make this impossible with the new dependency system. If I *must*
specify CASCADE to drop an object, my view depends on, my view
will be gone. If I don't CASCADE, I cannot drop the object.

So there is no way left to break the view temporarily (expert
mode here, I know what I do so please let me) and fix it later by
just reparsing the views definition.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being
right. #
# Let's break this rule - forgive
me. #
#==================================================
JanWieck@Yahoo.com #

On Wed, 2002-07-17 at 09:56, Jan Wieck wrote:

Joe Conway wrote:

The problem is that you would still need to keep a copy of your view
around to recreate it if you wanted to drop and recreate a table it
depends on. I really like the idea about keeping the original view
source handy in the system catalogs.

This has been the case all the time. I only see an attempt to
make this impossible with the new dependency system. If I *must*
specify CASCADE to drop an object, my view depends on, my view
will be gone. If I don't CASCADE, I cannot drop the object.

So there is no way left to break the view temporarily (expert
mode here, I know what I do so please let me) and fix it later by
just reparsing the views definition.

As somebody said, this is the place where CREATE OR REPLACE TABLE could
be useful. (IMHO it should recompile dependent views/rules/...
automatically or mark them as broken if compilation fails)

-------------
Hannu

On Wed, 2002-07-17 at 09:56, Jan Wieck wrote:

Joe Conway wrote:

The problem is that you would still need to keep a copy of your view
around to recreate it if you wanted to drop and recreate a table it
depends on. I really like the idea about keeping the original view
source handy in the system catalogs.

This has been the case all the time. I only see an attempt to
make this impossible with the new dependency system. If I *must*
specify CASCADE to drop an object, my view depends on, my view
will be gone. If I don't CASCADE, I cannot drop the object.

So there is no way left to break the view temporarily (expert
mode here, I know what I do so please let me)

I guess the real expert could manipulate pg_depends ;)

and fix it later by just reparsing the views definition.

---------
Hannu