Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

janus (3624)

janus
  (email not shown publicly)
http://janus.errornet.de/

Journal of janus (3624)

Tuesday April 06, 2010
07:15 AM

PostgreSQL rules and rocks

[ #40300 ]

... because one can build amazing database APIs with it by using views to their full extent:

BEGIN;

CREATE TABLE moo (
        id uuid NOT NULL PRIMARY KEY,
        relation regclass NOT NULL
);

CREATE FUNCTION moo(regclass, uuid) RETURNS uuid LANGUAGE sql AS $$
INSERT INTO moo (id, relation) VALUES ($2, $1) RETURNING id;
$$;

CREATE TABLE foo (
        id uuid NOT NULL DEFAULT uuid_generate_v1() PRIMARY KEY REFERENCES moo,
        name text NOT NULL UNIQUE
);

CREATE VIEW foos AS SELECT id, name FROM foo;

ALTER VIEW foos ALTER COLUMN id SET DEFAULT uuid_generate_v1();

CREATE RULE "_INSERT" AS ON INSERT TO foos DO INSTEAD
        INSERT INTO foo (id, name) SELECT moo('foo'::regclass, NEW.id), NEW.name RETURNING *;

INSERT INTO foos (id, name) VALUES ('17ee2d5a-4164-11df-be2f-0019dbf67458', 'a') RETURNING *;
INSERT INTO foos (id, name) VALUES ('1cf57cb8-4164-11df-9159-0019dbf67458', 'b') RETURNING *;
INSERT INTO foos (name) VALUES ('c') RETURNING *;

COMMIT;
... results in:


BEGIN
psql:rules-and-rocks.sql:7: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "moo_pkey" for table "moo"
CREATE TABLE
CREATE FUNCTION
psql:rules-and-rocks.sql:16: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
psql:rules-and-rocks.sql:16: NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_name_key" for table "foo"
CREATE TABLE
CREATE VIEW
ALTER VIEW
CREATE RULE
                                    id | name
--------------------------------------+------
  17ee2d5a-4164-11df-be2f-0019dbf67458 | a
(1 row)

INSERT 0 1
                                    id | name
--------------------------------------+------
  1cf57cb8-4164-11df-9159-0019dbf67458 | b
(1 row)

INSERT 0 1
                                    id | name
--------------------------------------+------
  e087171c-4174-11df-856c-0019dbf67458 | c
(1 row)

INSERT 0 1
COMMIT

But take care with rules... is what i've been told nearly agressively... because they're dangerous and come with pitfalls.
More gently said: RTFM! Twice! ;-)

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.