Limiting the occurence of a value in PostgreSQL

Question

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.

• user1822: Unrelated, but: why do you use count(%I)? If you think that is faster than count(*) then you are wrong - it's actually slower (assuming you do not intend to only count non-null values)
• Curtis Poe: Thank, a_horse_with_no_name. Fixed!

Answer 1

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) ->> this_column)::int; 
EXECUTE FORMAT('SELECT COUNT(*) FROM %I WHERE %I = 1ドル', this_table, this_column)
 USING this_column_value
 INTO total;

• Curtis Poe: Thank you. That's giving me ERROR: operator does not exist: bigint = text ... LINE 1: ...CT COUNT(end_node) FROM fibre_route_edge WHERE end_node = 1ドル for the query QUERY: SELECT COUNT(end_node) FROM fibre_route_edge WHERE end_node = 1ドル. However, it's not clear to me what's in 1ドル. It's probably not the value of the column because that's a BIGINT. I tried casting it (::integer) in both the quoted string and the USING section and both times I get ERROR: invalid input syntax for integer: "end_node", so it looks like that string is passed in, not the value of that column.
• user1822: Ah, I didn't see that this_column is a string that contains a column name. See my edit for a possible workaround.
• Curtis Poe: Thank you! That appears to have done the trick. Tests are now in place. Thank you!