Getting "missing FROM-clause" error when adding ST_Intersects constraint to PostGIS table

Question

I have two tables in PostGIS: afrivs, which is a polyline of various rivers, and weirs2 which is an empty polyline layer where potential dam sites will be added.

Obviously dams have to cross a river to work so I would like to add a constraint to enforce this. Following the examples here https://spatialdbadvisor.com/postgis_tips_tricks/127/how-to-apply-spatial-constraints-to-postgis-tables I tried adding the following constraint to check that any new dam polyline crosses a river polyline:

ALTER TABLE weirs2 ADD CONSTRAINT river_intersect 
CHECK (ST_Intersects(afrivs.geom,weirs2.geom)=true);

However this gives me the following error message.

ERROR:  missing FROM-clause entry for table "afrivs"

I also tried framing it as a SELECT query to include a FROM clause then get syntax error at or near "SELECT".

Based on this https://dba.stackexchange.com/questions/149466/missing-from-clause-entry-with-constraint-on-value-from-type-in-postgresql, I tried putting the table names in () but then get:

ERROR: column "afrivs" does not exist

What am I missing?

Using POSTGIS 2.4.4 r16526 on PostgreSQL 10.6.

Answer 1

It worked! Thanks ThingumaBob.

For reference the code I used:

CREATE FUNCTION check_intersect(gm GEOMETRY)
RETURNS boolean AS $$
    SELECT EXISTS (
      SELECT 1
      FROM   afrivs AS tb
      WHERE  tb.geom && gm
        AND  ST_Intersects(tb.geom, gm));
  $$ LANGUAGE sql

ALTER TABLE weirs2 
  ADD CONSTRAINT check_intersection
  CHECK (check_intersect(geom))

For bonus points:

• It seems inelegant to have to create a new function just to get two tables into one ST function - is there no easier way of doing this?

• Do I need SELECT EXISTS or would SELECT 1 also work?

• is there any way of having the afrivs table as an argument rather than hard-coding it into the function?

• what is the point of the WHERE tb.geom && gm line - is this just to check that they are both non-null?

Cheers,