Thread: create or replace rule/view

Attached is a patch implementing:

create or replace rule ...
create or replace view ...

It passes all regression tests. There's only one really sketchy part of
the patch: UpdateAttributeTuples(). This routine is fairly dangerous since
it simply removes a given relid's pg_attribute entries and creates a new
set basic on a given TupleDesc. Naturally, it is only useful for views.

It is important to have this functionality so that you can do something
like:

template1=# create view abc as select 'test'::text as a;
CREATE VIEW
template1=# select * from abc;
  a
------
 test
(1 row)

template1=# create or replace view abc as select '1'::int2 as a;
CREATE VIEW
template1=# select * from abc;
 a
---
 1
(1 row)

Regardless, the patch may need some cleaning up. Unfortunately, I don't
have enough time on my hands over the coming week. Considering beta is
coming up I thought it best to submit and see what happens.

Gavin

Gavin Sherry wrote:
> Attached is a patch implementing:
>
> create or replace rule ...
> create or replace view ...
>
> It passes all regression tests. There's only one really sketchy part of
> the patch: UpdateAttributeTuples(). This routine is fairly dangerous since
> it simply removes a given relid's pg_attribute entries and creates a new
> set basic on a given TupleDesc. Naturally, it is only useful for views.
>

This might be useful for the new named composite type feature also.
Assuming both of our patches get accepted, I'll look at implementing
CREATE OR REPLACE TYPE blah AS (...)

It'll be tough getting it into 7.3 though.

This just raised a question in my mind though. In nodeFunctionscan,
function_getonetuple(), I recently added the following in support of
anonymous composite types:

if (returnsTuple)
{
     slot = (TupleTableSlot *) retDatum;

     /*
      * if function return type was RECORD, we need to check to be
      * sure the structure from the query matches the actual return
      * structure
      */
     if (fn_typtype == 'p' && fn_typeid == RECORDOID)
         if (tupledesc_mismatch(tupdesc, slot->ttc_tupleDescriptor))
             elog(ERROR, "Query-specified return tuple and actual"
             " function return tuple do not match");

Should this check be applied *whenever* a function returns a tuple,
whether or not the function returns RECORD? Maybe:

if (returnsTuple)
{
     slot = (TupleTableSlot *) retDatum;

     /*
      * we need to check to be sure the specified structure matches
      * the actual return structure
      */
     if (tupledesc_mismatch(tupdesc, slot->ttc_tupleDescriptor))
         elog(ERROR, "specified column definitions and actual"
         " return column definitions do not match");

Comments?

Joe

Gavin Sherry <swm@linuxworld.com.au> writes:
> It passes all regression tests. There's only one really sketchy part of
> the patch: UpdateAttributeTuples(). This routine is fairly dangerous since
> it simply removes a given relid's pg_attribute entries and creates a new
> set basic on a given TupleDesc. Naturally, it is only useful for views.

You can NOT allow CREATE OR REPLACE VIEW to change the tupledesc of the
view, so I stopped reading right here --- take it out and install
prevention instead.  Why do you think that REPLACE VIEW is interesting?
It's so you can modify a view without breaking things that depend on it
... and things that depend on it depend on the tupledesc.  This is
exactly analogous to not allowing REPLACE FUNCTION to change the return
type of the function.

            regards, tom lane

Le Dimanche 11 Août 2002 19:22, Gavin Sherry a écrit :
> create or replace rule ...
> create or replace view ...

Thank you so much.
This will help pgAdmin2 a lot.

We hope your patch will be accepted before the next release of PostgreSQL. We
will make use of these new CREATE OR REPLACE features immediately in
pgAdmin2.

Again, thanks
Jean-Michel POURE