Limiting the occurence of a value in PostgreSQL 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(*) &lt;= 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 &gt;= 0 AND VALUE &lt; 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) &lt;&gt; 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 &gt; 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.
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: 

this_column_value := (to_jsonb(new) -&gt;&gt; this_column)::int; 
EXECUTE FORMAT('SELECT COUNT(*) FROM %I WHERE %I = $1', this_table, this_column)
  USING this_column_value
  INTO total;