1

I am trying to return the count of overlapping polygons. The problem is that it complains about my "$1" signs:

Error is "SQL state: 42601", syntax error at "$1". 

Why is this? I'm a total beginner here and I can't find the solution online, other than that parameters is referenced to with $ sign.

CREATE OR REPLACE FUNCTION any_overlap (x text) 
RETURNS integer AS $$
DECLARE amount INTEGER;
BEGIN
    SELECT COUNT(*) INTO amount FROM $1 a
    INNER JOIN $1 b ON 
    (a.polygon && b.polygon AND ST_Relate(a.polygon, b.polygon, '2********'))
    WHERE a.ctid != b.ctid;
    RETURN amount AS id;
END; $$ LANGUAGE plpgsql;
1
  • Is x supposed to be a table name? You're passing it as a text value, which makes your SELECT statements nonsensical
    – matigo
    May 18 '21 at 1:09
4

Problems:

  1. You cannot parameterize identifiers (like table names) in plain SQL. You need dynamic SQL using EXECUTE for this.
  2. amount is integer, but count() returns bigint.
  3. RETURN amount AS id; is invalid syntax. To force a particular column name for a returned scalar (not a set-returning function), use an OUT parameter. Example:
  4. We are talking about a function, not a stored procedure. See:

This function would work:

CREATE OR REPLACE FUNCTION any_overlap (_tbl regclass, OUT id bigint) 
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format(
   $q$
   SELECT count(*)
   FROM   %1$s a
   JOIN   %1$s b ON a.polygon && b.polygon AND st_relate(a.polygon, b.polygon, '2********')
   WHERE  a.ctid <> b.ctid
   $q$, _tbl)
   INTO id;
END
$func$;

Call:

SELECT any_overlap ('public.mytable');

Be wary of SQL injection when using dynamic SQL! Note the type regclass for the passed table name. See:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.