Обсуждение: HOWTO: FK: BIGINT[] -> BIGINT(Theoreticaly AnyElem[] -> AnyElem)

Поиск
Список
Период
Сортировка

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

От
"Oleg Serov"
Дата:
<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>