New column modifier?

On 04/29/2015 10:03 AM, John McKown wrote:

I am wondering about useful something might be. So I hope ya'll don't
mind me throwing out for feedback. I am fairly good with standard SQL,
but not the more advanced DBA things such as TRIGGERs. I am reading good
book, "PostgreSQL Server Programming" to increase my knowledge. The
section that I'm on now is about triggers. And how to use them to
enforce a "read only" column. So I'm wondering if such is very common? I
can see some uses for it with things which "should not change", such as
a system generated date/time in an audit log. Given how much PostgreSQL
extends beyond the ANSI standard, I'm wondering if a new column
modifier, perhaps "WORM" (Write Once, Read Many) might be of any real
use. Or is this "need" just not prevalent enough to require such a thing?

just thinking. But maybe not productively.

See here

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

http://www.postgresql.org/docs/9.4/static/sql-grant.html

and

http://www.postgresql.org/docs/9.4/static/sql-revoke.html

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

What you suggest is a new column type, and that would be something that
needs to be reviewed and implemented by the PostgreSQL developers. Since it
is not a SQL standard, I doubt it has much chance of implementation. That
being said, you should understand that all triggers require the need for an
associated trigger function. A simpler way to accomplish "WORM" is with a
RULE.
EG:
CREATE RULE table_worm AS ON UPDATE TO your_table WHERE NEW.your_column <>
OLD your_column DO NOTHING.

http://www.postgresql.org/docs/9.3/interactive/sql-createrule.html

On Wed, Apr 29, 2015 at 1:03 PM, John McKown <john.archie.mckown@gmail.com>
wrote:

I am wondering about useful something might be. So I hope ya'll don't mind
me throwing out for feedback. I am fairly good with standard SQL, but not
the more advanced DBA things such as TRIGGERs. I am reading good book,
"PostgreSQL Server Programming" to increase my knowledge. The section that
I'm on now is about triggers. And how to use them to enforce a "read only"
column. So I'm wondering if such is very common? I can see some uses for it
with things which "should not change", such as a system generated date/time
in an audit log. Given how much PostgreSQL extends beyond the ANSI
standard, I'm wondering if a new column modifier, perhaps "WORM" (Write
Once, Read Many) might be of any real use. Or is this "need" just not
prevalent enough to require such a thing?

just thinking. But maybe not productively.

--
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Wed, Apr 29, 2015 at 1:05 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:

What you suggest is a new column type, and that would be something that
needs to be reviewed and implemented by the PostgreSQL developers. Since it
is not a SQL standard, I doubt it has much chance of implementation. That
being said, you should understand that all triggers require the need for an
associated trigger function. A simpler way to accomplish "WORM" is with a
RULE.
EG:
CREATE RULE table_worm AS ON UPDATE TO your_table WHERE NEW.your_column <>
OLD your_column DO NOTHING.

http://www.postgresql.org/docs/9.3/interactive/sql-createrule.html

​Thanks. I "jumped the gun" and haven't gotten to RULEs in the book yet.
Lucky I like to read & have some time right now.​

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

--
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

On 4/29/15 1:05 PM, Joshua D. Drake wrote:

[ discussion about read-only columns ]

See here

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )

What I don't like about relying on GRANT is that the table owner gets to
bypass all that, as does a superuser. So when I'm serious about an
operation (insert, update or delete) not happening on something, I put a
trigger in place. Obviously a table owner or SU can always disable that,
but they can't do it accidentally. I would love the ability to restrict
operations both at a table and a column level.

BTW, John, you mentioned RULEs elsewhere... be very careful about using
those. They're incredibly easy to get wrong and generally not worth the
trouble.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general