plpgsql, fk inherited tables - Mailing list pgsql-general

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