Thread: Insert Updates Deletes on Views; Triggers on System Tables
New to Postgres so please excuse my ignorance. 1) Are inserts, updates and deletes on views permissible? 2) Can triggers be applied to system tables ( pg_group)? The reason for 2) is that each user MUST belong to ONLY one of six groups So I believe a before-trigger is required to enforce this rule. And speaking of rules, cannot utilize the Postgres rules system (presume similiar to Apache rewrite engine) as database MUST remain very "traditional Oracle" centric; hence the functions, triggers, etc. Raymond
Raymond <support@bigriverinfotech.com> writes: > New to Postgres so please excuse my ignorance. > > 1) Are inserts, updates and deletes on views permissible? Only using rules (which you say you can't do). > 2) Can triggers be applied to system tables ( pg_group)? Nope, not currently. > The reason for 2) is that each user MUST belong to ONLY one of six groups So I > believe a before-trigger is required to enforce this rule. And speaking of > rules, cannot utilize the Postgres rules system (presume similiar to Apache > rewrite engine) as database MUST remain very "traditional Oracle" centric; > hence the functions, triggers, etc. I don't see the point of this--obviously your function definitions, DDL etc will require a slightly different syntax for the PG database, so why not add rules in if you want updatable views? I don't think you'll be able to use the same DDL in both DBs if you're doing anything even slightly complicated, so... User code shouldn't see any differences between updatable views in the two systems, AFAIK. -Doug
Doug McNaught <doug@mcnaught.org> writes:
> Raymond <support@bigriverinfotech.com> writes:
>> believe a before-trigger is required to enforce this rule. And speaking of
>> rules, cannot utilize the Postgres rules system (presume similiar to Apache
>> rewrite engine) as database MUST remain very "traditional Oracle" centric;
>> hence the functions, triggers, etc.
> I don't see the point of this--obviously your function definitions,
> DDL etc will require a slightly different syntax for the PG database,
> so why not add rules in if you want updatable views?
It seems to me to make sense to use any functionality for which you
can find an equivalent in the other DB. Thus, there's nothing wrong
with writing some rules to construct an updatable view, if you can
create the equivalent updatable view in Oracle.
Taking a narrower view of compatibility than that will just leave you
fighting with one hand tied behind your back --- and, more than likely,
stuck with Oracle in the long run. I've never seen an Oracle DB that
didn't have some nonstandard Oracle-isms in it somewhere.
You may be able to use the same DML commands in both systems, but
requiring DDL to be the same will be a losing game.
regards, tom lane
Gang, I just inherited a postgresql database that does NOT have unique column names across the database. I'm wondering if there's some switch in postgresql/JDBC2 that I can throw to make it return the columns in the form of tablename.columnname, rather then just by columnname. I've looked through all the documentation, but can't find anything that would let me do something like this... ...what it does now. database=# select * from member; id | firstname | lastname | etc -----+---------------+--------------+-------- 2 | larry | lewis | 3 | dave | johnson | ...what I'd like it to do. database=# select * from member; member.id | member.firstname | member.lastname | member.etc ------------+----------------------+---------------------+-------------- 2 | larry | lewis | 3 | dave | johnson | ...this would make my life MUCH easier, and allow me to get some work done this century. I'm using straight-up Tomcat 4 with postgresql JDBC2 drivers. Thoughts? Suggestions? Would this be a JDBC driver thing, or postgresql thing? Thanks! /kurt
On Sun, 2003-07-13 at 00:13, Kurt Overberg wrote: > Gang, > > I just inherited a postgresql database that does NOT have unique column > names across the database. I'm wondering if there's some switch in > postgresql/JDBC2 that I can throw to make it return the columns in the > form of tablename.columnname, rather then just by columnname. I've > looked through all the documentation, but can't find anything that would > let me do something like this... [snip trivial example] What's wrong with having non-unique column names? Many places strive for that, since it makes visualizing key relationships easier. -- +-----------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | 4 degrees from Vladimir Putin +-----------------------------------------------------------+
On Sat, Jul 12, 2003 at 01:22:01PM -0700, Raymond wrote:
> 1) Are inserts, updates and deletes on views permissible?
Yes, but you need to write rules for this. Momjian's book (available
from A-W, I believe, or through the PostgreSQL web site) has a
discussion of how to do it.
> 2) Can triggers be applied to system tables ( pg_group)?
No.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110