<div dir="ltr">(Sorry for my bad english comments)<br />Demo sql:<br /><br />BEGIN; -- Begins the magic<br />CREATE
TABLEpk ( -- pk table<br /> id BIGINT,<br /> CONSTRAINT pk2_id PRIMARY KEY (id)<br />);<br /><br />CREATE TABLE
fk( -- fk table<br /> fk_ids BIGINT[]<br />);<br /><br />CREATE FUNCTION bia2bi(bigint[]) RETURNS bigint -- temp
typecast <br /> AS $$<br />SELECT $1[array_lower($1, 1)];<br />$$<br />LANGUAGE sql;<br /><br />CREATE FUNCTION
bi_fk(bigint,bigint[]) RETURNS boolean --FK FUNCTION FOR TABLE pk<br /> AS $$<br /> BEGIN<br /> IF (SELECT
count(*)FROM pk WHERE id = ANY($2)) = (array_upper($2, 1) - array_lower($2, 1) + 1) THEN<br /> RETURN TRUE;<br />
ENDIF;<br /> RAISE EXCEPTION 'NO FK![%, %]', $1, $2; -- RAISE ERROR MSG, and dont scan other million records<br />
END;<br/>$$<br />LANGUAGE 'plpgsql';<br /><br />CREATE OPERATOR ==> ( -- Cutsom operator for calling bi_fk<br />
PROCEDURE= bi_fk,<br /> LEFTARG = bigint,<br /> RIGHTARG = bigint[]);<br /><br />CREATE CAST (bigint[] AS bigint)
--TEMP CAST FOR INDEX CREATION<br /> WITH FUNCTION bia2bi(bigint[])<br /> AS IMPLICIT;<br /><br />ALTER TABLE
"fk"-- CREATE FK<br /> ADD CONSTRAINT "fk_id" FOREIGN KEY ("fk_ids")<br /> REFERENCES "pk"("id")<br /> ON
DELETENO ACTION<br /> ON UPDATE NO ACTION<br /> NOT DEFERRABLE;<br />DROP CAST(bigint[] AS bigint); -- REMOVE
CAST(recomended,but not needed)<br /><br />UPDATE pg_constraint <br /> SET conpfeqop = ARRAY(SELECT OID FROM
pg_operatorWHERE oprname = '==>')<br /> WHERE conname = 'fk_id'; -- CHANGING COMPARE FUNCTION<br /><br /><br
/>INSERTINTO pk SELECT k FROM GENERATE_series(1, 100000) as k; -- MAKE DATA<br /><br />INSERT INTO fk VALUES(ARRAY[1,2,
3]);-- TESTING<br />INSERT INTO fk VALUES(ARRAY[6,3,5]); -- TESTING<br /> INSERT INTO fk
VALUES(ARRAY[6,3,444444444444444445]);-- FK BREAK.<br />ROLLBACK; -- Magic rollback =)<br /></div>
