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_pkey" 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_pkey" 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_pkey" 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";
