/* Polymorphic foreign keys */

CREATE OR REPLACE FUNCTION null_zero(anyelement)
RETURNS INTEGER
LANGUAGE SQL
AS $$
    SELECT CASE WHEN $1 IS NULL THEN 0 ELSE 1 END;
$$;

CREATE TABLE r1 (
  r1_id SERIAL PRIMARY KEY
, r1_text TEXT
);

INSERT INTO r1 (r1_text)
VALUES ('foo bar')

CREATE TABLE r2 (
  r2_id SERIAL PRIMARY KEY
, r2_text_array TEXT[]
);

INSERT INTO r2 (r2_text_array)
VALUES ('{"baz","blurf"}');

CREATE TABLE r3 (
  r3_id SERIAL PRIMARY KEY
, r3_poly POLYGON
);
INSERT INTO r3 (r3_poly)
VALUES ( '((1,2),(3,4),(5,6),(7,8))' );

CREATE TABLE flex_key_shadow (
  flex_key_shadow_id SERIAL PRIMARY KEY
, r1_id INTEGER REFERENCES r1(r1_id)
, r2_id INTEGER REFERENCES r2(r2_id)
, r3_id INTEGER REFERENCES r3(r3_id)
);

ALTER TABLE flex_key_shadow ADD CONSTRAINT only_one_r
    CHECK(
      null_zero(r1_id)
    + null_zero(r2_id)
    + null_zero(r3_id)
    = 1)
;

CREATE VIEW flex_key AS
SELECT
  flex_key_shadow_id
, CASE
  WHEN r1_id IS NOT NULL THEN 'r1'
  WHEN r2_id IS NOT NULL THEN 'r2'
  WHEN r3_id IS NOT NULL THEN 'r3'
  ELSE 'wtf?!?'
  END AS "referenced_table"
, CASE
  WHEN r1_id IS NOT NULL THEN r1_id
  WHEN r2_id IS NOT NULL THEN r2_id
  WHEN r3_id IS NOT NULL THEN r3_id
  ELSE NULL
  END AS "referenced_id"
FROM flex_key_shadow
;