Thread: Insert Updates Deletes on Views; Triggers on System Tables

Insert Updates Deletes on Views; Triggers on System Tables

From
Raymond
Date:
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

Re: Insert Updates Deletes on Views; Triggers on System Tables

From
Doug McNaught
Date:
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

Re: Insert Updates Deletes on Views; Triggers on System Tables

From
Tom Lane
Date:
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

Returning tablename.column?

From
Kurt Overberg
Date:
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



Re: Returning tablename.column?

From
Ron Johnson
Date:
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
+-----------------------------------------------------------+


Re: Insert Updates Deletes on Views; Triggers on System Tables

From
Andrew Sullivan
Date:
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