CONSTRAINT to check values from a remotely related table (via join etc.)
CHECK
constraints cannot currently reference other tables. The manual:
Currently,
CHECK
expressions cannot contain subqueries nor refer tovariables other than columns of the current row.
One way is to use a trigger like demonstrated by @Wolph.
A clean solution without triggers: add redundant columns and include them in FOREIGN KEY
constraints, which are the first choice to enforce referential integrity. Related answer on dba.SE with detailed instructions:
Another option would be to "fake" an IMMUTABLE
function doing the check and use that in a CHECK
constraint. Postgres will allow this, but be aware of possible caveats. Best make that a NOT VALID
constraint. See:
A CHECK
constraint is not an option if you need joins. You can create a trigger which raises an error instead.
Have a look at this example: http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE
CREATE TABLE emp ( empname text, salary integer, last_date timestamp, last_user text);CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$ BEGIN -- Check that empname and salary are given IF NEW.empname IS NULL THEN RAISE EXCEPTION 'empname cannot be null'; END IF; IF NEW.salary IS NULL THEN RAISE EXCEPTION '% cannot have null salary', NEW.empname; END IF; -- Who works for us when she must pay for it? IF NEW.salary < 0 THEN RAISE EXCEPTION '% cannot have a negative salary', NEW.empname; END IF; -- Remember who changed the payroll when NEW.last_date := current_timestamp; NEW.last_user := current_user; RETURN NEW; END;$emp_stamp$ LANGUAGE plpgsql;CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
...i did it so (nazwa=user name, firma = company name) :
CREATE TABLE users( id bigserial CONSTRAINT firstkey PRIMARY KEY, nazwa character varying(20), firma character varying(50));CREATE TABLE test( id bigserial CONSTRAINT firstkey PRIMARY KEY, firma character varying(50), towar character varying(20), nazwisko character varying(20));ALTER TABLE public.test ENABLE ROW LEVEL SECURITY;CREATE OR REPLACE FUNCTION whoIAM3() RETURNS varchar(50) as $$declare result varchar(50); BEGIN select into result users.firma from users where users.nazwa = current_user; return result; END; $$ LANGUAGE plpgsql;CREATE POLICY user_policy ON public.test USING (firma = whoIAM3());CREATE FUNCTION test_trigger_function()RETURNS trigger AS $$BEGIN NEW.firma:=whoIam3();return NEW;END$$ LANGUAGE 'plpgsql'CREATE TRIGGER test_trigger_insert BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE test_trigger_function();