Postgresql – Using parameters in stored procedure with plpgsql

dynamic-sqlplpgsqlpostgispostgresqlsyntax

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;

Best Answer

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:

Related Question