'syntax error at or near "VARCHAR"' in DECLARE section when trying to create function

Question

I've created the following stored procedure which should return all the location names that had an intersection with polygon group (without distinct... intersection with 3 polygons => 3 names)

CREATE OR REPLACE FUNCTION get_name_without_distinct_by_polygon_group(start_time TIMESTAMP,
                                                                      end_time TIMESTAMP,
                                                                      polygon_group_id INTEGER)
RETURNS TABLE(name_name_name_name VARCHAR(12)) AS $$
DECLARE
  name VARCHAR(12);
  poly_id INTEGER;
BEGIN
  FOR poly_id IN (SELECT polygon_id 
                  FROM polygon_group_members 
                  WHERE group_id = poly_id)
    LOOP
      FOR name IN (SELECT DISTINCT name
                   FROM location, polygons
                   WHERE location.timestamp BETWEEN start_time AND end_time
                        AND poly_id = polygons.id
                        AND st_intersects(location.polygon, polygons.polygon))
      LOOP
        RETURN NEXT name;
      END LOOP;
  END LOOP;
  RETURN;
END;

$$ LANGUAGE SQL;

When I tried to create this function I got the following error:

[42601] ERROR: syntax error at or near "VARCHAR" Position: 356

Based on PostgreSQL declaration documentation it seems OK ...

What I've tried so far:

I changed name VARCHAR(12); to name location.mac%TYPE
I changed name VARCHAR(12); to name RECORD
I changed the scope of the declaration then I got the same error on poly_id INTEGER;
I changed the LANGUAGE SQL to postgressql
I googled it and according to any example/problem I found this procedure should work.

[There are no "stored procedures" in Postgres, just functions](http://stackoverflow.com/a/33896307/939860). And are you linking to Postgres 9.1 documentation because that's your actual version? — Erwin Brandstetter, Dec 15 '15 at 15:04

score 2 · Answer 1 · answered Dec 15 '15 at 12:11

You don't need PL/pgSQL for this. The loops are unnecessary and will make everything very slow.

As far as I can tell this should do it:

CREATE OR REPLACE FUNCTION get_name_without_distinct_by_polygon_group(start_time TIMESTAMP,
                                                                      end_time TIMESTAMP,
                                                                      polygon_group_id INTEGER)
   RETURNS TABLE(name_name_name_name VARCHAR(12)) 
AS 
$$
  SELECT DISTINCT name
  FROM location, polygons
  WHERE location.timestamp BETWEEN start_time AND end_time
    AND poly_id IN (SELECT polygon_id  FROM polygon_group_members WHERE group_id = polygon_group_id)
    AND st_intersects(location.polygon, polygons.polygon));
$$ 
LANGUAGE SQL;

I also think that the condition WHERE group_id = poly_id is wrong as you are using the variable that should store the result in the where clause. I think you meant to use WHERE group_id = polygon_group_id (I changed that in the above code)

When using language sql you cannot use procedural code like begin ... end or declare variables.

The error ERROR: syntax error at or near "VARCHAR" Position: 356 is caused by using language sql but using PL/pgSQL inside the function body. If you change language sql to language plpgsql in your definition, it should work (but again that solution having two nested loops is not very efficient).

You are right about the `LANGUAGE plpgsql` (in the comment you've deleted...) then I got a new error which I am working on it... However as I wrote in the question there is no distinct on names (BTW location name is not a primary key). the first loop gives me all the polygons in the group. the second loop gives me all the names that are intersect with the polygon. the procedure will return all the names without distinct so if a location has intersection with 3 polygons(over the time, the locations are not fixed) I'll have 3 names in the result... — Old Fox, Dec 15 '15 at 12:36

score 2 · Answer 2 · answered Dec 15 '15 at 12:57

Thanks to @a_horse_with_no_name's comment which point me out that $$ LANGUAGE SQL should be $$ LANGUAGE plpgsql I found the solution. After I changed to $$ LANGUAGE plpgsql I got a new error about missing OUT PARAMETER, but OUT PARAMETER is not allowed with RETURN NEXT so I had to return SETOF instead of TABLE:

CREATE OR REPLACE FUNCTION get_name_without_distinct_by_polygon_group(start_time TIMESTAMP,
                                                                      end_time TIMESTAMP,
                                                                      polygon_group_id INTEGER)
RETURNS SETOF VARCHAR(12) AS $$
DECLARE
  name VARCHAR(12);
  poly_id INTEGER;
BEGIN
  FOR poly_id IN (SELECT polygon_id 
                  FROM polygon_group_members 
                  WHERE group_id = poly_id)
    LOOP
      FOR name IN (SELECT DISTINCT name
                   FROM location, polygons
                   WHERE location.timestamp BETWEEN start_time AND end_time
                        AND poly_id = polygons.id
                        AND st_intersects(location.polygon, polygons.polygon))
      LOOP
        RETURN NEXT name;
      END LOOP;
  END LOOP;
  RETURN;
END;

$$ LANGUAGE plpgsql;

If a_horse's answer was useful to you like you state, consider actually voting for it - like the tooltip suggests "This answer is useful". — Erwin Brandstetter, Dec 15 '15 at 14:56
@ErwinBrandstetter actually I vote up for his comment. he had a comment on the question which was deleted... his answer was not helpful at all... however your answer is better then my solution(+1 and accept). I just waited to test it.... — Old Fox, Dec 20 '15 at 08:50

score 1 · Accepted Answer · edited May 23 '17 at 11:59

Your posed answer still has a couple of problems. And you can very well use RETURNS TABLE(). Compare:

plpgsql error "RETURN NEXT cannot have a parameter in function with OUT parameters" in table-returning function:

CREATE OR REPLACE FUNCTION get_name_without_distinct_by_polygon_group(start_time timestamp,
                                                                      end_time timestamp,
                                                                      polygon_group_id int)
  RETURNS TABLE(name_name_name_name VARCHAR(12)) AS
$func$
DECLARE
   name VARCHAR(12);  -- possible naming conflict!
   poly_id int;
BEGIN
   FOR poly_id IN  --  no parentheses needed
      SELECT polygon_id
      FROM   polygon_group_members 
      WHERE  group_id = poly_id  -- I suspect you really want polygon_group_id here
   LOOP
      FOR name_name_name_name IN  -- assign directly
         SELECT DISTINCT name  -- l.name or p.name??
         FROM   polygons p
         JOIN   location l ON st_intersects(l.polygon, p.polygon)
         WHERE  p.id = poly_id
         AND    l.timestamp BETWEEN start_time AND end_time
      LOOP
        RETURN NEXT;  -- already assigned
      END LOOP;
   END LOOP;
   RETURN;
END
$func$  LANGUAGE plpgsql;

Be aware of possible naming conflicts. All declared variable and parameters (including fields in the RETURNS TABLE() clause are visible in SQL queries inside the body of a plpgsql or SQL function. A widespread convention would be to prepend variable names with _ and table-qualify all columns in queries. See earlier answer from today:

Check for integer in string array

The whole function could probably be replaced with a single SELECT statement.

'syntax error at or near "VARCHAR"' in DECLARE section when trying to create function

3 Answers3