Using PostgreSQL 11.7 and I have to model some real-world constraints. Effectively, I'm modeling a graph and an edge, of course, has two nodes. Each node can have zero, one, or two edges. Thus, I need a constraint similar to:
node UUID NOT NULL CHECK (SELECT true FROM this_table WHERE node = NEW.node HAVING COUNT(*) <= 2)
Except that this syntax is illegal. I also have the following problem since this applies to four of our tables:
- I don't know the table name up front
- I don't know the column name up front
- I don't know the allowed number of occurrences up front
I almost have it working, but I can't figure out how to extract the value of the NEW.node
. I have written a trigger that's close, but no cigar:
CREATE DOMAIN uint2 AS int4 CHECK(VALUE >= 0 AND VALUE < 65536);
CREATE OR REPLACE FUNCTION limit_count() RETURNS TRIGGER AS $$
DECLARE
total integer;
this_table text := TG_TABLE_NAME::regclass::text;
this_column text := TG_ARGV[0]; -- first argument (triggers cannot define arguments directly)
this_limit uint2 := TG_ARGV[1]; -- second argument
BEGIN
IF cardinality(TG_ARGV) <> 2 THEN
RAISE EXCEPTION 'limit_count() trigger expects a column name and a limit';
END IF;
EXECUTE FORMAT('SELECT COUNT(%I) FROM %I WHERE %1$I = $1', this_column, this_table)
USING NEW.quote_literal(this_column)
INTO total;
IF total + 1 > this_limit THEN
RAISE EXCEPTION '%.% has % occurences, but must not have more than %', this_table, this_column, total, this_limit;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER fibre_route_edge_start_node_limit_trigger BEFORE INSERT OR UPDATE ON fibre_route_edge
FOR EACH ROW EXECUTE PROCEDURE limit_count('start_node', 2);
CREATE TRIGGER fibre_route_edge_end_node_limit_trigger BEFORE INSERT OR UPDATE ON fibre_route_edge
FOR EACH ROW EXECUTE PROCEDURE limit_count('end_node', 2);
The above fails with:
ERROR: schema "new" does not exist
LINE 1: SELECT NEW.quote_literal(this_column)
^
QUERY: SELECT NEW.quote_literal(this_column)
Happy to solve this problem without a trigger, but I'm kinda stumped right now. And examples I've seen using hstore and other tools have been incomprehensible to me.
Best Answer
You can't access the fields of the
new
record dynamically using names stored in a variable.The only way I can think of, is to convert the NEW record into a JSON value, then you can access the value "dynamically" through the name: