Hi!
I'm trying to get the unique foreign key feature for inherited tables.
There's a problem with plpgsql that I don't understand, could you see
what's wrong?
Comments are well appreciated.
I created a function that given a <name>, tries to return the sequence from
<name>_id_seq
CREATE FUNCTION next_seq_inh_key(text) RETURNS int4 AS '
DECLARE
rec RECORD;
t text;
BEGIN
t := $1 || ''_id_seq'';
SELECT INTO rec nextval( t::text) as id;
RETURN rec.id;
END;
' LANGUAGE 'plpgsql';
This works well when invoked manually from psql.
But from the following function (that is triggered) it doesn't work
properly, it yields the following
ERROR: parser: parse error at or near "$1"
CREATE FUNCTION update_tree() RETURNS OPAQUE AS ' --
DECLARE
sequence int4;
rec RECORD;
treename text;
currtable text;
keytable text;
typetable text;
t text;
BEGIN
treename := TG_ARGV[0];
currtable := TG_RELNAME;
sequence := next_seq_inh_key(treename); -- !!! this is problemful
-- t := treename || ''_id_seq''; -- !!! even this way is problemful
-- sequence := nextval( t::text); -- !!!
keytable := ''inhkeys_'' || treename;
typetable := ''inhtypes_'' || treename;
IF TG_OP = ''INSERT'' THEN
SELECT INTO rec * FROM typetable WHERE "tablename" = currtable;
INSERT INTO keytable ("id", "type") VALUES (sequence, rec.type);
NEW.id := sequence;
RETURN NEW;
ELSEIF TG_OP = ''DELETE'' THEN
DELETE FROM keytable WHERE "id" = OLD.id;
-- ELSEIF TG_OP = ''UPDATE'' --
END IF;
END;
' LANGUAGE 'plpgsql';
=============================================
Here's the complete script that you may use for testing the problem:
CREATE FUNCTION next_seq_inh_key(text) RETURNS int4 AS '
DECLARE
rec RECORD;
-- n int4;
t text;
BEGIN
t := $1 || ''_id_seq'';
SELECT INTO rec nextval( t::text) as id;
RETURN rec.id;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION update_tree() RETURNS OPAQUE AS ' --
DECLARE
sequence int4;
rec RECORD;
treename text;
currtable text;
keytable text;
typetable text;
t text;
BEGIN
treename := TG_ARGV[0];
currtable := TG_RELNAME;
-- sequence := next_seq_inh_key(treename);
t := treename || ''_id_seq'';
sequence := nextval( t::text);
keytable := ''inhkeys_'' || treename;
typetable := ''inhtypes_'' || treename;
IF TG_OP = ''INSERT'' THEN
SELECT INTO rec * FROM typetable WHERE "tablename" = currtable;
INSERT INTO keytable ("id", "type") VALUES (sequence, rec.type);
NEW.id := sequence;
RETURN NEW;
ELSEIF TG_OP = ''DELETE'' THEN
DELETE FROM keytable WHERE "id" = OLD.id;
-- ELSEIF TG_OP = ''UPDATE'' -- c
END IF;
END;
' LANGUAGE 'plpgsql';
-------------------------------------------------
CREATE SEQUENCE "people_id_seq" increment 1 minvalue 10000 maxvalue
2147483600 start 10000 cache 1 ;
SELECT * from "people_id_seq";
CREATE TABLE "inhkeys_people" (
"id" int4, -- the unique key across all elements across inherited tables
"type" int2
);
CREATE UNIQUE INDEX inhkeys_uidx_id ON inhkeys_people(id);
CREATE TABLE "inhtypes_people" (
"type" int2,
"tablename" text
);
INSERT INTO "inhtypes_people" (type, tablename)
VALUES (10, 'people');
CREATE TABLE "people" (
"id" int4,
"info" text
);
CREATE TRIGGER people_refkey BEFORE INSERT OR DELETE ON people
FOR EACH ROW EXECUTE PROCEDURE update_tree('people');
ALTER TABLE "people" ADD CONSTRAINT people_fk_inhkeys_people
FOREIGN KEY (id) REFERENCES inhkeys_people(id) INITIALLY DEFERRED;
CREATE UNIQUE INDEX people_uidx_id ON people(id);
INSERT INTO "inhtypes_people" (type, tablename)
VALUES (20, 'student');
CREATE TABLE "student" (
"matr" text
) inherits ("people");
ALTER TABLE "student" ADD CONSTRAINT student_fk_inhkeys_people
FOREIGN KEY (id) REFERENCES inhkeys_people(id) INITIALLY DEFERRED;
CREATE UNIQUE INDEX student_uidx_id ON student(id);
CREATE TRIGGER student_refkey BEFORE INSERT OR DELETE ON student
FOR EACH ROW EXECUTE PROCEDURE update_tree('people');
INSERT INTO student ('info', 'matr') VALUES ('blabla', '333');
Nico
