Using parameters in stored procedure with plpgsql

Question

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;

Answer 1

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:
   • Get latest timestamp from column in given table dynamically
4. We are talking about a function, not a stored procedure. See:
   • In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

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:

• Table name as a PostgreSQL function parameter