Table partitioning and Rules

[Girish]

I have a set of tables partitioned horizontally. DML below. I have also
created a view that will allow me to display information stored in all
partitioned tables.

Im trying to create an INSERT RULE on the VIEW that would direct an insert
into the appropriate partition table depending on the personal LastName.

Can anyone help me with this?
Thanks,
Girish
-- Table: public."contact _A_G"
CREATE TABLE public."contact _A_G" (
"CONTACTID" int8 NOT NULL,
"LastName" varchar(50),
"FirstName" varchar(50),
CONSTRAINT "contact_A_G_pk ey" PRIMARY KEY ("CONTACTID" )
) WITH OIDS;

-- Table: public."contact _H_N"
CREATE TABLE public."contact _H_N" (
"CONTACTID" int8 NOT NULL,
"LastName" varchar(50),
"FirstName" varchar(50),
CONSTRAINT "contact_H_N_pk ey" PRIMARY KEY ("CONTACTID" )
) WITH OIDS;

-- Table: public."contact _O_Z"
CREATE TABLE public."contact _O_Z" (
"CONTACTID" int8 NOT NULL,
"LastName" varchar(50),
"FirstName" varchar(50),
CONSTRAINT "contact_O_Z_pk ey" PRIMARY KEY ("CONTACTID" )
) WITH OIDS;
CREATE VIEW Contact AS
SELECT * FROM "Contact_A_ G"
UNION
SELECT * FROM "Contact_H_ M"
UNION
SELECT * FROM "Contact_N_ Z";

[rstp]

You would probably want to use a function instead of a rule for this.

I would recommend changing all table and column names to lower case, it
makes it easier to write queries. You might also look at changing the
contactid column to type SERIAL, so you wouldn't have to supply the
value, postgres would increment it automagically. And maybe have the
function just call the INSERT statement once, so it's easier to maintain.

Here's one way you could approach it:

CREATE OR REPLACE FUNCTION insert_contact( integer, varchar, varchar)
RETURNS text AS '
DECLARE
contact_id ALIAS FOR $1;
last_name ALIAS FOR $2;
first_name ALIAS FOR $3;
ret_val boolean;
BEGIN
ret_val := FALSE;
IF upper(last_name ) BETWEEN \'A\' AND \'G\' THEN
INSERT INTO contact_a_g (contactid, lastname, firstname) VALUES
(contact_id, last_name, first_name);
ELSIF upper(last_name ) BETWEEN \'H\' AND \'N\' THEN
INSERT INTO contact_h_n (contactid, lastname, firstname) VALUES
(contact_id, last_name, first_name);
ELSE
INSERT INTO contact_o_z (contactid, lastname, firstname) VALUES
(contact_id, last_name, first_name);
END IF;

ret_val := TRUE;
return ret_val;
END;
' LANGUAGE 'plpgsql';

To call it, you would use:
SELECT insert_contact( 1, 'Bajaj', 'Girish');
and it would return TRUE on successful completion.

Of course you'll have to make sure that you've added plpqsql support
(see createlang).

HTH
Ron

Girish wrote:

I have a set of tables partitioned horizontally. DML below. I have also
created a view that will allow me to display information stored in all
partitioned tables.

Im trying to create an INSERT RULE on the VIEW that would direct an insert
into the appropriate partition table depending on the personal LastName.

Can anyone help me with this?
Thanks,
Girish
-- Table: public."contact _A_G"
CREATE TABLE public."contact _A_G" (
"CONTACTID" int8 NOT NULL,
"LastName" varchar(50),
"FirstName" varchar(50),
CONSTRAINT "contact_A_G_pk ey" PRIMARY KEY ("CONTACTID" )
) WITH OIDS;

-- Table: public."contact _H_N"
CREATE TABLE public."contact _H_N" (
"CONTACTID" int8 NOT NULL,
"LastName" varchar(50),
"FirstName" varchar(50),
CONSTRAINT "contact_H_N_pk ey" PRIMARY KEY ("CONTACTID" )
) WITH OIDS;

-- Table: public."contact _O_Z"
CREATE TABLE public."contact _O_Z" (
"CONTACTID" int8 NOT NULL,
"LastName" varchar(50),
"FirstName" varchar(50),
CONSTRAINT "contact_O_Z_pk ey" PRIMARY KEY ("CONTACTID" )
) WITH OIDS;
CREATE VIEW Contact AS
SELECT * FROM "Contact_A_ G"
UNION
SELECT * FROM "Contact_H_ M"
UNION
SELECT * FROM "Contact_N_ Z";