Using parameters in stored procedure with plpgsql I am trying to return the count of overlapping polygons. The problem is that it complains about my &quot;$1&quot; signs:

Error is &quot;SQL state: 42601&quot;, syntax error at &quot;$1&quot;. 


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 &amp;&amp; b.polygon AND ST_Relate(a.polygon, b.polygon, '2********'))
    WHERE a.ctid != b.ctid;
    RETURN amount AS id;
END; $$ LANGUAGE plpgsql;
Problems:

You cannot parameterize identifiers (like table names) in plain SQL. You need dynamic SQL using EXECUTE for this.
amount is integer, but count() returns bigint.
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


We are talking about a function, not a stored procedure. See:

In PostgreSQL, what is the difference between a &quot;Stored Procedure&quot; 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 &amp;&amp; b.polygon AND st_relate(a.polygon, b.polygon, '2********')
   WHERE  a.ctid &lt;&gt; 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