Re: UPDATEABLE VIEWS ... Examples?

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" smallint DEFAULT 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")
ON DELETE 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" AS
SELECT r.reagent_id AS supernatant_id, r.created, r.modified,
r.version, r.batch_id, r.barcode, s.vendor_id
FROM abbase.reagents r
JOIN 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 INSTEAD
DELETE FROM "abbase"."reagents" WHERE reagent_id =
OLD.supernatant_id;

CREATE RULE "r_supernatants_delete" AS ON DELETE TO
"abbase"."r_supernatants"
DO
DELETE 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 INSTEAD
DELETE 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 %]"
DO
DELETE 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(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Marc G. Fournier
Sent: Thursday, June 16, 2005 5:05 PM
To: pgsql-sql(at)postgresql(dot)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(at)hub(dot)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