varlena
varlena
PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

28-Feb-2005 Issue: 99

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see www.PostgreSQL.org.

RSS Feed for General Bits
RSS 21-Feb-2005

An RSS feed for PostgreSQL General Bits is now available for those who use RSS. It will contain the issues for the current month along with titles of articles in the month.

Happy Feeding!

Editor: elein at varlena.com
New PostgreSQL Brazilian Site
[pgsql-advocacy] new brazilian site 21-Feb-2005

Diogo Biazus reports that the new Brazilian PostgreSQL site went live with a brand new look. Check out
http://www.postgresql.org.br/.

Contributors: elein at varlena.com
Using Upper-Case Identifiers
[GENERAL] Problem with inserting data into a table..... 22-Feb-2005

PostgreSQL folds all identifiers, or names, into lower-case unless they were created with double quotes. Identifiers are object names like table and column names and function names for example.

This table shows the resultant PostgreSQL table name when creating a table with and without quotes and with and without capitalization. It also shows how the name should be used in an insert statement.

CreateResult Table NameInsert
create table foofoo insert into foo values (...);
create table "foo"foo insert into foo values (...);
create table Barbar insert into bar values (...);
create table "Bar""Bar" insert into "Bar" values (...);

You must use the double quoted table name whenever you created the table with double quotes and it contains capitalized letters.

It is important to remember that pgAdminIII helps out when you create tables and columns. It double quotes the identifiers for you, resulting in names that then require double quoting when referencing them if the identifier contains upper-case letters.

Contributors: Sharon Abu Sharon_Abu at PacketLight.com FERREIRA William (COFRAMI) william.ferreira at airbus.com Richard Huxton dev at archonet.com Ragnar Hafsta? gnari at simnet.is
Deleting from Master Detail View
[GENERAL] Is this correct behavior for ON DELETE rule? 25-Feb-2005

We have a master and a detail table of items and books. The manipulation of books is to be done strictly through a view, bookview. The insertion into a bookview causes an insertion into both the item table and the book table. A delete from the book view should delete both the item and the book records.

Usually all inserts, deletes and updates on views are done with DO INSTEAD rules. However, due to the scope of the OLD record in the RULE, the delete functionality is a bit tricky.

Here is the table, view and rule creation.drop table book cascade;

DROP TABLE item CASCADE;
DROP TABLE book CASCADE;

-- master table
CREATE TABLE item
      (id SERIAL PRIMARY KEY,
      type  text,
      title text
);

-- detail table
CREATE TABLE book
      (id     integer REFERENCES item PRIMARY KEY,
      title text,
      author  text
);

-- combine stuff from item and book tables
CREATE VIEW bookview AS
      SELECT i.id, b.title, b.author FROM item i, book b
      WHERE i.id=b.id;

-- insert to item and book instead of bookview
CREATE RULE bookviewins AS ON INSERT TO bookview DO INSTEAD (
      INSERT INTO item (type, title)
      VALUES ('book', NEW.title);
      INSERT INTO book (id, title, author)
      VALUES (currval('item_id_seq'), NEW.title, NEW.author);
);

-- delete to item and book instead of bookview
CREATE RULE bookviewdel AS ON DELETE TO bookview DO INSTEAD (
      DELETE FROM book WHERE id=OLD.id;
      DELETE FROM item WHERE id=OLD.id;
);
INSERT INTO bookview (title, author) VALUES ('Dune','Herbert');
INSERT INTO bookview (title, author) VALUES ('Hobbit','Tolkein');

db=# SELECT * FROM bookview;
 id | title  | author
----+--------+---------
  1 | Dune   | Herbert
  2 | Hobbit | Tolkein
(2 rows)

db=# DELETE FROM bookview WHERE author='Tolkein';
DELETE 0

db=#  SELECT * FROM bookview;
 id | title | author
----+-------+---------
  1 | Dune  | Herbert
(1 row)

db=#  SELECT * FROM item;
 id | type | title
----+------+--------
  1 | book | Dune
  2 | book | Hobbit
(2 rows)

db=#  SELECT * FROM book;
 id | title | author
----+-------+---------
  1 | Dune  | Herbert
(1 row)
This insert rule works as expected. However, the delete rule does not; the delete from item fails. One would think that reversing the order of the deletes might help but it does not. When the book is deleted, it is no longer visible in the view and so the OLD.id does not exist anymore causing the delete from item to fail.

The proper way to enforce the deletion of both the book and item is to define the relationships between book and item so that delete cascades.

CREATE TABLE book
      (id     integer REFERENCES item ON DELETE CASCADE PRIMARY KEY,
      title text,
      author  text
);
With ON DELETE CASCADE defined like this, the book is deleted when the item is deleted. With this in force, the delete RULE for bookview should look like this:
CREATE RULE bookviewdel AS ON DELETE TO bookview DO INSTEAD (
      DELETE FROM item WHERE id=OLD.id;
);
Rerunning the selects and delete shows here that the delete is of item cascades properly to the book table.
db=# SELECT * FROM bookview;
 id | title | author
----+-------+---------
  1 | Dune  | Herbert
(1 row)

db=# DELETE FROM bookview WHERE author='Tolkein';
DELETE 0
db=# SELECT * FROM bookview;
 id | title | author
----+-------+---------
  1 | Dune  | Herbert
(1 row)

db=# SELECT * FROM item;
 id | type | title
----+------+-------
  1 | book | Dune
(1 row)

db=# -- shows both books
db=# SELECT * FROM book;
 id | title | author
----+-------+---------
  1 | Dune  | Herbert
(1 row)


Contributors: Rick Schumeyer rschumeyer at ieee.org, Bruce Momjian pgman at candle.pha.pa.us, Tom Lane tgl at sss.pgh.pa.us, Keary Suska hierophant at pcisys.net


Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Top
Google
Search General Bits & varlena.com Search WWW