Saturday, March 11, 2006

Serial types in postgresql views using INSERT INSTEAD rules

Discovered a little trick last week in postgres. When you want to write to a table via its view instead of directly by using a rule it can be tricky to get the serial primary key values to set properly. What you want is for the view to behave in just the same way as a normal table. So when you provide a value for the serial it uses that, otherwise it just autoincrements.


First set up a backend table:

CREATE TABLE my_data (
id serial primary key,
somevalue text,
--more data
);

Using a serial value will create the sequence my_data_id_seq which we can use later.
Next create the view:

CREATE VIEW my_view AS SELECT id,somevalue... FROM my_data;

Now here is the trick. I want to insert data into my_view instead of my_data directly but the data needs to actually be stored in my_data. We do this:

CREATE RULE my_view_insert AS ON INSERT TO my_view DO INSTEAD
INSERT INTO my_data (id,somevalue) VALUES (COALSECE(NEW.id, nextval('my_data_id_seq'::text)),NEW.somevalue);

The real kicker is the COALSECE(NEW.id, nextval('my_data_id_seq'::text)) bit. It says that if an id is explicitly set then use that otherwise use the next value in the sequence. Neat, huh?

0 Comments:

Post a Comment

<< Home