Nikolay Samokhvalov wrote:
On 08/10/05, Martijn van Oosterhout <kleptog@xxxxxxxxx> wrote:
On Sat, Oct 08, 2005 at 03:32:00PM +0400, Nikolay Samokhvalov wrote:
http://chernowiki.ru/Dev/PostgreSQLComparedWithSQL2003AndOracleSQLServerDB2Etc
Perhaps I'm wrong with some issues - any comments are welcome.
The only thing I can comment on is updatable views. You can make
updatable views using RULEs. The only thing is that they're not
*automatically* updateable.
OK, I'll make this correction. But for me, updatable views are views
for which DBMS supports insert/update/delete operations as for
tables. Ideally, people shouldn't distinguish table and view - that's
what theory stands for (see Date's thoutghs about it:
http://www.dbmsmag.com/int9410.html, he also has a cycle of articles
on this theme: http://www.dbdebunk.citymax.com/page/page/622302.htm).
Who do you mean by "people?" Users? DBA's?
Now, obviously the DBA will always be able to distinguish between them.
Otherwise he/she wouldn't be much of a DBA would he/she?
PostgreSQL doesn't support updates even for simple views such as
select-with-restriction. What it does support - not updatable views,
but some kind of INSTEAD OFF triggers (another form of).
Ok... Your complaint is that PostgreSQL doesn't support automatically
generating insert/update/delete rules for views. Ultimately, however,
this may not be done simply because there are other priorities which
people are devoting their time and energy to instead. I know some
people have put in some work on this item but I don't know what the
status is.
Also, what SQL Server 2003 calles a trigger, we call a rule. What we
call a trigger is different and I am not sure SQL Server 2003 has such
an equivalent.
In theory, if someone came up will a program that from given <view
definition> produced the appropriate INSERT, UPDATE and DELETE rules,
it might be incorporated. Currently you just have to do it manually...
There is a good theory (Date), but it cannot be implemented for any
practical DBMS. The cause lies in differences between theory and
practice. And the major difference is possibility to define tables w/o
PK (in other words, possible duplicate rows). Nevertheless, all major
commercial RDMSs support some subset of views that can be updated..
SQL:2003 defines a quite large subset, but the definition is pretty
mazy...
Again, this is likely doable. There are hidden fields that I suppose
could be extended in a view to reference unique rows (maybe ctid since
the whole thing is expanded in a single SQL statement, but I haven't
tried it).
Best Wishes,
Chris Travers
Metatron Technology Consulting
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
