Using geometry in PL\pgSQL functions?

Question

I am trying to create a function in PL\pgSQL procedural language of two geometric data types, multipoint and multiline string for example. I want to select all lines within 30 meter distance for all points. Here is what I have tried:

Create OR Replace Function get_streets(bar.geom geometry foo.geom geometry)
Returns geometry AS $$
BEGIN
  IF ST_DWithin(bar.geom, foo.geom, 30.0) Then
    Return foo.geom;
  ELSE
    Return null;
  END IF
  Return foo.geom;
END;
$$ Language plpgsql;

The function returns an error because of geometry data type. Any advice for using/handling geometry data in plpgsql functions?

Patrick Patrick · Accepted Answer · 2016-03-20T18:47:37

You have quite a few small syntax mistakes in your function. See the version below for a working function:

CREATE FUNCTION get_streets(g1 geometry, g2 geometry) RETURNS geometry AS $$
BEGIN
  IF ST_DWithin(g1, g2, 30.0) THEN
    RETURN g2;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Most importantly: do not use table.field notation for the parameters. Instead, use a simple name and use the appropriate field when you call the function:

SELECT get_streets(bar.geom, foo.geom)
FROM bar
JOIN foo on ...

Incidentally, you can get the same functionality without using a function at all:

SELECT foo.geom
FROM foo
JOIN bar ON ST_DWithin(bar.geom, foo.geom, 30)
WHERE <other conditions>;

Using geometry in PL\pgSQL functions?

1 Answers