CREATE VIEW my_view AS
SELECT id, 'Resource1' AS type FROM Resource1
UNION ALL
SELECT id, 'Resource2' AS type FROM Resource2;

CREATE TABLE Permission (
  user_id INTEGER,
  resource_id INTEGER,
  resource_type TEXT,
  role TEXT,
  -- Add a check constraint to ensure the resource_type is either 'Resource1' or 'Resource2'
  CHECK (resource_type IN ('Resource1', 'Resource2')),
  -- Add a foreign key constraint with deferrable option to allow for deferred checks
  FOREIGN KEY (resource_id, resource_type) my_view (id) DEFERRABLE INITIALLY DEFERRED
);

CREATE OR REPLACE FUNCTION check_foreign_id() RETURNS TRIGGER AS $$
BEGIN
  -- Your desired conditions based on the query
  IF NEW.foreign_id NOT IN (SELECT id FROM some_table WHERE condition = true) THEN
    RAISE EXCEPTION 'Foreign key constraint violation.';
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER check_foreign_id_trigger
BEFORE INSERT OR UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION check_foreign_id();

create table Permission(user_id   integer not null 
                                  references users(id) 
          , resource1_id  integer references resource1(id) 
          , resource2_id  integer references resource1(id) 
          , resource_type text
            -- exactly 1 of resource ids must be null
          , constraint exactly_one_resource check( num_nulls(resource1_id, resource2_id) = 1)
            -- not null resource id must agree with resource_type
          , constraint id_matches_type check (   resource_type = 'Resource1' and resource1_id is not null
                                              or resource_type = 'Resource2' and resource2_id is not null
                                              )  
                                              
          );