Thread: UPDATEABLE VIEWS ... Examples?

Reading through the docs, both the CREATE VIEW and CREATE RULE pages refer 
to how you can use a RULE to 'simulate' an updateable VIEW ... but I can't 
seem to find any examples of this ...

Does anyone know of an online example of doing this that I can read 
through?

Thanks ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

There is a write up on these at:
http://www.varlena.com/GeneralBits/82.php

--elein
============================================================
elein@varlena.com        Varlena, LLC        www.varlena.com
         PostgreSQL Consulting, Support & Training   

PostgreSQL General Bits   http://www.varlena.com/GeneralBits/
=============================================================
I have always depended on the [QA] of strangers.


On Thu, Jun 16, 2005 at 06:05:03PM -0300, Marc G. Fournier wrote:
> 
> Reading through the docs, both the CREATE VIEW and CREATE RULE pages refer 
> to how you can use a RULE to 'simulate' an updateable VIEW ... but I can't 
> seem to find any examples of this ...
> 
> Does anyone know of an online example of doing this that I can read 
> through?
> 
> Thanks ...
> 
> ----
> Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
> Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
> 

Here's one I did a while ago; the tables are trivial in this case (and
the whole thing is definitely overkill) so it should make it easier to
digest.

This becomes useful if you use some sort of ORM layer (Class::DBI in my
case) that can be made to recognize the 'type' column and behave
polymorphically.

The nice part is that I can use these classes in my CRUD framework
without any special treatment, the downside is that the whole thing is
just more trouble than it's worth.

At the end I've included a script that generates the rules for you,
given the tables and the view.

CREATE TABLE "abbase"."reagents" ( "reagent_id"    serial NOT NULL, "type"        varchar(15) DEFAULT 'base' NOT NULL,
"created"       timestamp DEFAULT now() NOT NULL, "modified"    timestamp DEFAULT now() NOT NULL, "version"
smallintDEFAULT 0 NOT NULL, "batch_id"    integer NOT NULL, "barcode"        char(6) NOT NULL 
) WITH OIDS;

ALTER TABLE "abbase"."reagents" ADD PRIMARY KEY ("reagent_id");
ALTER TABLE "abbase"."reagents" ADD CONSTRAINT "batch"FOREIGN KEY ("batch_id")REFERENCES "abbase"."batches"
("batch_id")ONDELETE CASCADE ON UPDATE CASCADE DEFERRABLE; 
ALTER TABLE "abbase"."reagents" ADD CONSTRAINT types CHECK (type IN
('base', 'supernatant'));
CREATE INDEX "idx_reagents_barcode" ON "abbase"."reagents" ("barcode");

CREATE TABLE "abbase"."r_supernatants" ( "supernatant_id"    integer NOT NULL, "vendor_id"        varchar(25) NOT NULL
) WITH OIDS;

ALTER TABLE "abbase"."r_supernatants" ADD PRIMARY KEY
("supernatant_id");
ALTER TABLE "abbase"."r_supernatants" ADD CONSTRAINT "reagent"FOREIGN KEY ("supernatant_id")REFERENCES
"abbase"."reagents"("reagent_id")ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE; 

CREATE VIEW "abbase"."supernatants" ASSELECT r.reagent_id AS supernatant_id, r.created, r.modified,
r.version, r.batch_id, r.barcode, s.vendor_idFROM abbase.reagents rJOIN abbase.r_supernatants s ON(r.reagent_id =
s.supernatant_id)
;

CREATE RULE "supernatants_insert" AS ON INSERT TO
"abbase"."supernatants"DO INSTEAD (    INSERT INTO "abbase"."reagents" (reagent_id, type,
batch_id, barcode)    VALUES (        COALESCE(NEW.supernatant_id,
nextval('reagents_reagent_id_seq')),        'supernatant',        NEW.batch_id,        NEW.barcode    );    INSERT INTO
"abbase"."r_supernatants"(supernatant_id, 
vendor_id)    VALUES (        COALESCE(NEW.supernatant_id,
currval('reagents_reagent_id_seq')),        NEW.vendor_id    ););

CREATE RULE "supernatants_update" AS ON UPDATE TO
"abbase"."supernatants"DO INSTEAD (    UPDATE "abbase"."reagents" SET        type        = 'supernatant',
batch_id   = NEW.batch_id,        barcode        = NEW.barcode    WHERE reagent_id = OLD.supernatant_id;    UPDATE
"abbase"."r_supernatants"SET        vendor_id        = NEW.vendor_id    WHERE supernatant_id = OLD.supernatant_id;); 

CREATE RULE "supernatants_delete" AS ON DELETE TO
"abbase"."supernatants"DO INSTEADDELETE FROM "abbase"."reagents" WHERE reagent_id =
OLD.supernatant_id;

CREATE RULE "r_supernatants_delete" AS ON DELETE TO
"abbase"."r_supernatants"DODELETE FROM "abbase"."reagents" WHERE reagent_id =
OLD.supernatant_id;



Here's a script that generated the rules, it's not pretty but seems to
work:

#!/usr/bin/perl -w
use strict;

# autocreate rules for updating multi-table views

use Data::Dumper;
use Getopt::Long;
use IO::All;
use Template;
use POSIX qw(ceil);

########################################################################
########

my $d_exclude = {created        => 1,modified    => 1,version        => 1,
};

my $template = Template->new({INTERPOLATE  => 1,
}) || die "$Template::ERROR\n";

my $opts = {dmitri    => 0,autotype    => 1,
};

########################################################################
########

GetOptions($opts, 'base=s', 'join=s', 'type=s', 'view=s', 'dmitri',
'primary', 'autotype!');

warn "WARNING: dmitrisms are on, some assumptions may not make sense"
if($opts->{dmitri});
die "need the base class file (--base)" unless($opts->{base});
die "need the join class file (--join)" unless($opts->{join});
unless($opts->{type}){if($opts->{join} =~ /^\w_(\w+)s\.sql$/){    warn "WARNING: no 'type' specified for class,
guessing:
$1";    $opts->{type} = $1;}else {    die "need the join class type (--type)";}
}
unless($opts->{view}){$opts->{view} = $opts->{type}.'s';warn "WARNING: no view name specified, guessing:
".$opts->{view};
}

########################################################################
########

my $table_base = parse_create($opts->{base});
my $table_join = parse_create($opts->{join});
$table_base->{base} = 1;

foreach my $table ($table_base, $table_join){@{$table->{col_names}} = grep {!$d_exclude->{$_}}
@{$table->{col_names}} if($opts->{dmitri});foreach my $col (@{$table->{col_names}}){    my $val;    if($col eq 'type'
&&$opts->{autotype}){        $val = "'".$opts->{type}."'";    }    elsif($col eq $table->{primary}){        $val =
sprintf"COALESCE(NEW.%s, 
%s('%s_%s_seq'))", $table_join->{primary}, ($table->{base}) ? 'nextval'
: 'currval', $table_base->{name}, $table_base->{primary};    }    else {        $val = 'NEW.'.$col;    }    push
@{$table->{cols}},{name => $col, value => $val, 
len => length($col)};    }($table->{longest}) = sort {$b <=> $a} map {$_->{len}}
@{$table->{cols}};$_->{tabs} = ceil(($table->{longest} - $_->{len} + 2)/4)
for(@{$table->{cols}});
}

my $view = {name    => $opts->{view},schema    => $table_join->{schema},
};

$template->process(\*DATA, {    tbl_base    => $table_base,    tbl_join    => $table_join,    view        => $view,
}) or die $template->error;

########################################################################
########

sub parse_create {my $file = shift;my $table = {};my $sql = io($file)->slurp;$sql =~ s/^\s+//;$sql =~ s/\s+$//;$sql =~
s/\s+//g;if($sql =~ /\s*CREATE TABLE (\"?(\w+)\"?\.)?\"?(\w+)\"?/i){    $table->{schema} = $2 || 'public';
$table->{name}= $3;}else {    die "cannot find table name in '$sql'";}foreach my $line (split /,|\((?!=\))/, $sql){
push@{$table->{col_names}}, $1 if($line =~ 
/^\s*\"?(\w+)\" (bigint|int8|bigserial|serial8|bit|bit
varying|varbit|boolean|bool|box|bytea|character
varying|varchar|character|char|cidr|circle|date|double
precision|float8|inet|integer|int|int4|interval|line|lseg|macaddr|money|
numeric|decimal|path|point|polygon|real|float4|smallint|int2|serial|seri
al4|text|time|timetz|timestamp|timestamptz)/i);}$table->{primary} = $table->{col_names}->[0];return $table;
}

########################################################################
########

__DATA__
CREATE RULE "[% view.name %]_insert" AS ON INSERT TO "[% view.schema
%]"."[% view.name %]"DO INSTEAD (    [%- INCLUDE insert_table tbl = tbl_base -%]    [%- INCLUDE insert_table tbl =
tbl_join%]); 

CREATE RULE "[% view.name %]_update" AS ON UPDATE TO "[% view.schema
%]"."[% view.name %]"DO INSTEAD (    [%- INCLUDE update_table tbl = tbl_base -%]    [%- INCLUDE update_table tbl =
tbl_join%]); 

CREATE RULE "[% view.name %]_delete" AS ON DELETE TO "[% view.schema
%]"."[% view.name %]"DO INSTEADDELETE FROM "[% tbl_base.schema %]"."[% tbl_base.name %]" WHERE
[% tbl_base.primary %] = OLD.[% tbl_join.primary %];

CREATE RULE "[% tbl_join.name %]_delete" AS ON DELETE TO "[%
tbl_join.schema %]"."[% tbl_join.name %]"DODELETE FROM "[% tbl_base.schema %]"."[% tbl_base.name %]" WHERE
[% tbl_base.primary %] = OLD.[% tbl_join.primary %];

[%- BLOCK insert_table %]    INSERT INTO "[% tbl.schema %]"."[% tbl.name %]" ([%
FOREACH col = tbl.cols %][% col.name %][% UNLESS loop.last() %], [% END
%][% END %])    VALUES (    [%- FOREACH col = tbl.cols %]        [% col.value %][% UNLESS loop.last() %],[% END
%]    [%- END %]    );
[%- END -%]

[%- BLOCK update_table %]    UPDATE "[% tbl.schema %]"."[% tbl.name %]" SET
[% FOREACH col = tbl.cols -%]    [%- UNLESS col.name == tbl.primary -%]        [% SET tab = "\t" %][% col.name %][%
tab.repeat(col.tabs) %]= [% col.value %][% UNLESS loop.last() %],[% END
%][% "\n" %]    [%- END -%]
[%- END -%]    WHERE [% tbl.primary %] = OLD.[% tbl_join.primary %];
[%- END -%]

Dmitri


-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Marc G. Fournier
Sent: Thursday, June 16, 2005 5:05 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] UPDATEABLE VIEWS ... Examples?



Reading through the docs, both the CREATE VIEW and CREATE RULE pages
refer
to how you can use a RULE to 'simulate' an updateable VIEW ... but I
can't
seem to find any examples of this ...

Does anyone know of an online example of doing this that I can read
through?

Thanks ...

----
Marc G. Fournier           Hub.Org Networking Services
(http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ:
7615664

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your     joining column's datatypes do not match
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you
received this in error, please contact the sender and delete the
material from any computer

On Thu, Jun 16, 2005 at 06:05:03PM -0300, Marc G. Fournier wrote:
> 
> Reading through the docs, both the CREATE VIEW and CREATE RULE pages refer 
> to how you can use a RULE to 'simulate' an updateable VIEW ... but I can't 
> seem to find any examples of this ...

Are you looking for "Cooperation with Views" in the "Rules on INSERT,
UPDATE, and DELETE" section of the "The Rule System" chapter?

http://www.postgresql.org/docs/8.0/static/rules-update.html#RULES-UPDATE-VIEWS

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Dmitri Bichko wrote:
> warn "WARNING: dmitrisms are on, some assumptions may not make sense"

beauty!

:)


-- 
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________

> Reading through the docs, both the CREATE VIEW and CREATE RULE pages refer 
> to how you can use a RULE to 'simulate' an updateable VIEW ... but I can't 
> seem to find any examples of this ...

This is maybe a newbie question, but what is the difference with a
materialized view ?

David
-- 
dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.46.47.21.37

> This is maybe a newbie question, but what is the difference with a
> materialized view ?
>

An updateable view is one you can insert, delete or update rows.
A materialized view is one that is pre-executed... i mean, that the
data is stored in a file for fast execution of the view

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

> > This is maybe a newbie question, but what is the difference with a
> > materialized view ?
> 
> An updateable view is one you can insert, delete or update rows.
> A materialized view is one that is pre-executed... i mean, that the
> data is stored in a file for fast execution of the view

Thanks Jaime, it's perfectly clear now.

David

-- 
dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.46.47.21.37

On Jun 20, 2005, at 4:16 PM, David Pradier wrote:

> This is maybe a newbie question, but what is the difference with a
> materialized view ?

As I understand it (and that understanding may be wrong) a  
materialized view is a table that holds the values of a view from  
some point in time. A view is a "saved" subselect. Everything you  
call a view, the PostgreSQL backend re-evaluates the definition of  
the view. A materialized view is only as accurate as the last time it  
was updated. Things such as triggers can be used to keep the  
materialized view updated. The key issue with materialized views is  
to make sure it's always up-to-date with the evaluation of the view.

Michael Glaesemann
grzm myrealbox com

> >This is maybe a newbie question, but what is the difference with a
> >materialized view ?
> 
> As I understand it (and that understanding may be wrong) a  
> materialized view is a table that holds the values of a view from  
> some point in time. A view is a "saved" subselect. Everything you  
> call a view, the PostgreSQL backend re-evaluates the definition of  
> the view. A materialized view is only as accurate as the last time it  
> was updated. Things such as triggers can be used to keep the  
> materialized view updated. The key issue with materialized views is  
> to make sure it's always up-to-date with the evaluation of the view.

Thanks Michael, I already know what is a materialized view. I often use
them, systematically updated with some triggers.
It's an updatable view that i didn't knew.

It opens some perspectives in programmation, methinks :-)

Thanks all the same,
David

-- 
dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.46.47.21.37

An example(found it some time ago somewhere ?! :) ):

/*
drop view a_and_b cascade;
drop table tbla cascade;
drop table tblb cascade;
*/

CREATE TABLE tbla
( id int4 NOT NULL, a int4, b varchar(12), CONSTRAINT tbla_pk PRIMARY KEY (id)
)
--WITHOUT OIDS
;

CREATE TABLE tblb
( id int4 NOT NULL, x bool, y timestamp, CONSTRAINT tblb_pk PRIMARY KEY (id), CONSTRAINT tblb_fk FOREIGN KEY (id)
REFERENCEStbla (id) ON UPDATE CASCADE
 
ON DELETE CASCADE
)
--WITHOUT OIDS
;

INSERT INTO tbla VALUES ( 3, 9034, 'test1' );
INSERT INTO tbla VALUES ( 6, -23, 'test2' );
INSERT INTO tblb VALUES ( 3, false, now() );
INSERT INTO tblb VALUES ( 6, true, now() );

CREATE OR REPLACE VIEW a_and_b AS SELECT tbla.id, tbla.a, tbla.b, tblb.x, tblb.y   FROM tbla
NATURAL LEFT JOIN tblb;


CREATE OR REPLACE RULE a_b_insert AS

    ON INSERT TO a_and_b DO INSTEAD (    INSERT INTO tbla (id, a, b) VALUES (new.id, new.a, new.b);    INSERT INTO tblb
(id,x, y) VALUES (new.id, new.x, new.y);
 
);

-- test your insert
INSERT INTO a_and_b VALUES (99, 123, 'text', false, now() );


CREATE OR REPLACE RULE a_and_b_del AS

    ON DELETE TO a_and_b DO INSTEAD     DELETE FROM tbla WHERE tbla.id = OLD.id;

-- test your delete
DELETE FROM a_and_b WHERE id=99;

CREATE OR REPLACE RULE a_and_b_upd AS

    ON UPDATE TO a_and_b DO INSTEAD    (       UPDATE tbla SET a = new.a, b = new.b  WHERE tbla.id = new.id;
UPDATEtblb SET x = new.x, y = new.y  WHERE tblb.id = new.id ;    );
 

-- test your update
UPDATE a_and_b SET a=-333, b='neotext', x=false, y='2005-6-6' WHERE id=1;

... it works ok in pgadmin  ...

PS:   but for me is a problem - I can't do update from delphi7 :   Error is: "row cannot be located for updating"  ...
thisis because I  
 
do 2 updates in rule of     update view and the odbc driver (psqlodbc ) or  
delphi wants to do update based on every field     ... (also is no key in  
view!!!???)   ... if anybody have a solution to this problem ....!?

Best Regards,
Adrian Din,
Om Computer & SoftWare
Bucuresti, Romania

-- 
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/