Unable to execute the function in plpgsql/postgres

Question

I want to calculate distance from address points to all streets within a distance of 50 meters using plpgsql. I have tried the following function:

Create or Replace Function get_dist(ad geometry, st geometry)
Returns double precision AS
$$
Begin
Insert into street(Distance)
Select ST_Distance(ad.geom, st.geom) from ad
Left Join st ON ST_DWithin(ad.geom, st.geom, 50.0);
Return ST_Distance(ad.geom, st.geom);
End
$$
Language plpgsql volatile;

Creating the function gives no error but when I try to call it using this command:

Select get_dist(ad.geom, st.geom) from ad
Left Join st ON st.gid = ad.gid;

I get this error:

ERROR:  missing FROM-clause entry for table "ad"
LINE 1: SELECT ST_Distance(ad.geom, st.geom)

Can someone please highlight what is wrong with the function (creating the function and calling it)?

Answer 1

For processing a single row or processing rows one-by one, you can use the SQL RETURNING clause of INSERT combined with the plpgsql INTO clause. Example:

• Get default serial value after INSERT inside PL/pgSQL

But you are obviously trying to process a whole set at once.

calculate distance from address points to all streets within a distance of 50 meters ...

Use a set-based approach. Much faster and cleaner. If you want to return rows from the INSERT additionally use a set-returning function. Example:

• Return a query from a function?

You would not need a function at all. Just this query:

INSERT INTO street(ad_geom, st_geom, distance, traffic_ct)  -- any columns in street
SELECT ad.geom, st.geom, ST_Distance(ad.geom, st.geom), ad.traffic_ct
FROM   ad
LEFT   JOIN st ON ST_DWithin(ad.geom, st.geom, 50.0)
RETURNING *  -- all columns in street

I guess you don't actually need anything returned any more, since this query does all you wanted, but I kept RETURNING as proof of concept. You can just skip it.

Use [INNER] JOINinstead of LEFT [OUTER] JOIN if you don't want to include adresses with no matching street.

The manual about RETURNING in INSERT:

The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted [...] any expression using the table's columns is allowed. The syntax of the RETURNING list is identical to that of the output list of SELECT. Only rows that were successfully inserted or updated will be returned.

If you need to wrap this into a plpgsql function (could also be a simple SQL function) - and still return all rows:

CREATE OR REPLACE FUNCTION insert_neighbours()
  RETURNS SETOF street AS
$func$
BEGIN
   RETURN QUERY
   INSERT INTO street(ad_geom, st_geom, distance, traffic_ct)  -- any columns in street
   SELECT ad.geom, st.geom, ST_Distance(ad.geom, st.geom), ad.traffic_ct
   FROM   ad
   LEFT   JOIN st ON ST_DWithin(ad.geom, st.geom, 50.0)
   RETURNING *;  -- all columns in street
END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM insert_neighbours();  -- use SELECT * FROM ... !

For simplicity I return the whole row, but you can return select columns as well. See above example.

---

Creating the function gives no error

That's because PL/pgSQL currently only runs superficial syntax checks on CREATE FUNCTION. You have to actually execute the function to test it - and make sure that all branches of code in plpgsql functions get tested.