Thread: ORACLE COMMENT statement
Hello Bruce, I've just submitted a patch to the patches list which implements Oracle's COMMENT statement. It will insert/update/delete the appropriate OID for the table or column targeted for the comment. It should apply cleanly against current. If it passes your scrutiny, I was wondering a couple of things: 1. Might it be possible for psql (a.k.a Peter Eisentraut) to display the comments associated with tables, views, and columnsin its \d output? Or perhaps another \ command? 2. Should I write up SGML for it (as well as for TRUNCATE TABLE)? 3. Should I expand it beyond ORACLE's syntax to include functions, types, triggers, rules, etc.? On the TODO list its listed as: Allow pg_descriptions when creating types, tables, columns, and functions Anyways, Hope its worth something, Mike Mascari (mascarim@yahoo.com) ===== __________________________________________________ Do You Yahoo!? Bid and sell for free at http://auctions.yahoo.com
> Hello Bruce, > > I've just submitted a patch to the patches list which > implements Oracle's COMMENT statement. It will > insert/update/delete the appropriate OID for the > table or column targeted for the comment. It should > apply cleanly against current. If it passes your > scrutiny, I was wondering a couple of things: > > 1. Might it be possible for psql > (a.k.a Peter Eisentraut) to display the comments > associated with tables, views, and columns in > its \d output? Or perhaps another \ command? Sure. \dd does that already. > > 2. Should I write up SGML for it (as well as for > TRUNCATE TABLE)? I did that for Truncate. You can see it in the docs. If you want to write on on this, that would be good. It seems more complex. > > 3. Should I expand it beyond ORACLE's syntax to > include functions, types, triggers, rules, etc.? Sure, why not. \dd already handles it. > > On the TODO list its listed as: > > Allow pg_descriptions when creating types, tables, > columns, and functions Yep. Removed the 'table' entry, and marked it as done. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> 2. Should I write up SGML for it (as well as for
> TRUNCATE TABLE)?
Yes (though TRUNCATE TABLE has something already in
ref/truncate.sgml).
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
I have another question regarding this: It seems that you can attach a description (or comment, as it is) to any oid. (Not with this command, but in general). Is this restricted to system oids (like below 16000 or whatever it was)? Otherwise comments on any user tuple could be created. Perhaps this should be explicitly prevented or allowed. In the latter case perhaps the comment statement could be tweaked. Not sure. Just wondering. -Peter On Oct 14, Mike Mascari mentioned: > Hello Bruce, > > I've just submitted a patch to the patches list which > implements Oracle's COMMENT statement. It will > insert/update/delete the appropriate OID for the > table or column targeted for the comment. It should > apply cleanly against current. If it passes your > scrutiny, I was wondering a couple of things: > > 1. Might it be possible for psql > (a.k.a Peter Eisentraut) to display the comments > associated with tables, views, and columns in > its \d output? Or perhaps another \ command? > > 2. Should I write up SGML for it (as well as for > TRUNCATE TABLE)? > > 3. Should I expand it beyond ORACLE's syntax to > include functions, types, triggers, rules, etc.? > > On the TODO list its listed as: > > Allow pg_descriptions when creating types, tables, > columns, and functions -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
On Oct 14, Mike Mascari mentioned: > 1. Might it be possible for psql > (a.k.a Peter Eisentraut) to display the comments > associated with tables, views, and columns in > its \d output? Or perhaps another \ command? I was sort of sitting in the holes for the below TODO to get finished. I was thinking about the \d output as well, perhaps one could switch it on and off somewhere. I'll see what I can do. > Allow pg_descriptions when creating types, tables, > columns, and functions -Peter -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
The intent of the COMMENT ON statement was to allow for users to provide comments on user tables, views, and the fields which compose them as in ORACLE. I'll expand the syntax beyond ORACLE's to include rules, triggers, and functions. Of course, all of these are the non-system OIDs (although the PostgreSQL super-user could create/drop comments on system oid-related objects). The implication is that I'll have to modify pg_dump to generate COMMENT ON commands as well for all user tables, views, functions, triggers, and rules. The patch I submitted uses ORACLE's syntax which requires you to specify the schema object type as well as its name, such as: COMMENT ON TABLE employees IS 'Employee Records'; COMMENT ON COLUMN employees.employee IS 'Employee ID'; so I'll just add: COMMENT ON RULE... COMMENT ON TRIGGER... COMMENT ON FUNCTION... Hopefully, the Win32 ODBC driver is smart enough to fetch the comments from pg_description in response to a call to ::SQLTables or ::SQLColumns, so ODBC applications can see the user comments supplied (I'll check this). I don't know about JDBC. There's currently nothing to stop a user from performing an INSERT on pg_description using any OID they please. Perhaps that should be restricted, but who knows what applications are out there now which, not having a COMMENT ON statement, are storing user comments already in pg_description. Hopefully, I'll have the other forms done in the next few days. As Bruce pointed out, \dd already displays comments for any type. I was hoping for a single psql '\' command to display the table, its comments, its column definitions, and any comments associated with the columns...(an outer join SURE would be nice for that -- altough one could always do a SELECT ... WHERE join UNION SELECT WHERE NOT EXISTS..) Anyways, Hope that helps, Mike Mascari (mascarim@yahoo.com) --- Peter Eisentraut <peter_e@gmx.net> wrote: > I have another question regarding this: It seems > that you can attach a > description (or comment, as it is) to any oid. (Not > with this command, but > in general). Is this restricted to system oids (like > below 16000 or > whatever it was)? Otherwise comments on any user > tuple could be created. > Perhaps this should be explicitly prevented or > allowed. In the latter case > perhaps the comment statement could be tweaked. Not > sure. Just wondering. > > -Peter > > Peter Eisentraut Sernanders vaeg > 10:115 > peter_e@gmx.net 75262 Uppsala > http://yi.org/peter-e/ Sweden ===== __________________________________________________ Do You Yahoo!? Bid and sell for free at http://auctions.yahoo.com
On Oct 16, Mike Mascari mentioned:
> Hopefully, I'll have the other forms done in the next
> few days. As Bruce pointed out, \dd already displays
> comments for any type. I was hoping for a single
> psql '\' command to display the table, its comments,
> its column definitions, and any comments associated
> with the columns...(an outer join SURE would be
> nice for that -- altough one could always do a
> SELECT ... WHERE join UNION SELECT WHERE NOT EXISTS..)
I tell you, outer joins will be nice for a lot of things in psql. At this
point I'm not sure if I should break backwards compatibility like that,
but then psql is supposed to be sort of the example application, so the
latest technology ought to be used.
Anyway, the \dd command was kind of odd in that it only displayed comments
but not what the comments went with.
The way I currently have implemented the comments is like this:
(Ignoring the actual output format, which is currently under _heavy_
development.)
peter@localhost:5432 play=> \d foobar
Table "foobar"
Attribute | Type | Info
----------+--------------+---------
a | numeric(9,2) | not null
b | varchar(5) |
c | char(10) |
d | char(1) |
peter@localhost:5432 play=> \set description ""
peter@localhost:5432 play=> \d foobar
Table "foobar"
Attribute | Type | Info | Description
----------+--------------+----------+------------
a | numeric(9,2) | not null |
b | varchar(5) | |
c | char(10) | |
d | char(1) | |
peter@localhost:5432 play=> \l
List of databases
Database | Owner | Encoding | Description
----------+----------+----------+---------------------------------------
play | postgres | 0 |
pwdb | peter | 0 |
template1 | postgres | 0 |
twig | httpd | 0 | This is for that Twig mailer under PHP
(4 rows)
peter@localhost:5432 play=> \unset description
peter@localhost:5432 play=> \l
List of databases
Database | Owner | Encoding
----------+----------+---------
play | postgres | 0
pwdb | peter | 0
template1 | postgres | 0
twig | httpd | 0
(4 rows)
peter@localhost:5432 play=> \dd
Object descriptions
Name | What | Description
-------------------+----------+------------------------------------------
! | operator | fraction
!! | operator | fraction
!!= | operator | not in
!~ | operator | does not match regex., case-sensitive
!~* | operator | does not match regex., case-insensitive
!~~ | operator | does not match LIKE expression
# | operator | intersection point
--<snip>--
varcharne | function | not equal
varcharoctetlen | function | octet length
version | function | PostgreSQL version string
width | function | box width
xid | type | transaction id
xideq | function | equal
| | operator | start of interval
|/ | operator | square root
|| | operator | concatenate
||/ | operator | cube root
~ | operator | contains
~ | operator | matches regex., case-sensitive
~ | operator | path contains point?
~ | operator | polygon contains point?
~* | operator | matches regex., case-insensitive
~= | operator | same as
~~ | operator | matches LIKE expression
(973 rows)
peter@localhost:5432 play=> \dd version
Object descriptionsName | What | Description
--------+----------+--------------------------
version | function | PostgreSQL version string
(1 row)
Now if we just put a description on every pre-installed entity (in
particular system tables), this is almost like a built-in quick reference!
The \dd doesn't do rules yet, I think. But I'll put that in soon.
So do you see that as a feasible solution?
-Peter
--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden