HOWTO: FK: BIGINT[] -> BIGINT(Theoreticaly AnyElem[] -> AnyElem)

From: "Oleg Serov" <serovov(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: HOWTO: FK: BIGINT[] -> BIGINT(Theoreticaly AnyElem[] -> AnyElem)
Date: 2008-09-22 11:47:11
Message-ID: cec7c6df0809220447w16d131b7xd4ae0d481bd1d265@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

(Sorry for my bad english comments)
Demo sql:

BEGIN; -- Begins the magic
CREATE TABLE pk ( -- pk table
id BIGINT,
CONSTRAINT pk2_id PRIMARY KEY (id)
);

CREATE TABLE fk ( -- fk table
fk_ids BIGINT[]
);

CREATE FUNCTION bia2bi(bigint[]) RETURNS bigint -- temp type cast
AS $$
SELECT $1[array_lower($1, 1)];
$$
LANGUAGE sql;

CREATE FUNCTION bi_fk(bigint, bigint[]) RETURNS boolean --FK FUNCTION FOR
TABLE pk
AS $$
BEGIN
IF (SELECT count(*) FROM pk WHERE id = ANY($2)) = (array_upper($2, 1) -
array_lower($2, 1) + 1) THEN
RETURN TRUE;
END IF;
RAISE EXCEPTION 'NO FK![%, %]', $1, $2; -- RAISE ERROR MSG, and dont
scan other million records
END;
$$
LANGUAGE 'plpgsql';

CREATE OPERATOR ==> ( -- Cutsom operator for calling bi_fk
PROCEDURE = bi_fk,
LEFTARG = bigint,
RIGHTARG = bigint[]);

CREATE CAST (bigint[] AS bigint) -- TEMP CAST FOR INDEX CREATION
WITH FUNCTION bia2bi(bigint[])
AS IMPLICIT;

ALTER TABLE "fk" -- CREATE FK
ADD CONSTRAINT "fk_id" FOREIGN KEY ("fk_ids")
REFERENCES "pk"("id")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;
DROP CAST(bigint[] AS bigint); -- REMOVE CAST(recomended, but not needed)

UPDATE pg_constraint
SET conpfeqop = ARRAY(SELECT OID FROM pg_operator WHERE oprname = '==>')
WHERE conname = 'fk_id'; -- CHANGING COMPARE FUNCTION

INSERT INTO pk SELECT k FROM GENERATE_series(1, 100000) as k; -- MAKE DATA

INSERT INTO fk VALUES(ARRAY[1,2, 3]); -- TESTING
INSERT INTO fk VALUES(ARRAY[6,3,5]); -- TESTING
INSERT INTO fk VALUES(ARRAY[6,3,444444444444444445]); -- FK BREAK.
ROLLBACK; -- Magic rollback =)

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2008-09-22 11:51:43 Re: [patch] fix dblink security hole
Previous Message Hans-Jürgen Schönig 2008-09-22 11:34:04 Re: Initial prefetch performance testing