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.

Create	Result Table Name	Insert
create table foo	foo	insert into foo values (...);
create table "foo"	foo	insert into foo values (...);
create table Bar	bar	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)

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
);

CREATE RULE bookviewdel AS ON DELETE TO bookview DO INSTEAD (
      DELETE FROM item WHERE id=OLD.id;
);

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