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)?

Pass whole records - ad / st, or geometry... In the function - if ad/st is geometry - then rewrite inner select... — Michał Zaborowski, Jun 20 '16 at 09:07
How can you execute this function without getting errors? Are there tables `ad` and `st` in your database (since you are selecting from them)? Please describe in more detail what you are trying to accomplish. — Laurenz Albe, Jun 20 '16 at 09:37

Patrick · Answer 1 · 2016-06-20T12:47:26.973

1

What you appear to want is to calculate a distance between two geometries and then insert that distance in a table if it is than 50.0. The function would be like this:

CREATE FUNCTION get_dist(ad geometry, st geometry) RETURNS double precision AS $$
DECLARE
    dist double precision;
BEGIN
    dist := ST_Distance(ad, st);
    IF dist < 50.0 THEN
        INSERT INTO street(Distance) VALUES (dist);
    END IF;
    RETURN dist;
END;
$$ LANGUAGE plpgsql;

However, I doubt that you really want that. For starters, the inserted row in table street will be assigned distance = dist when the function is called and the condition met, but no other properties (except any default values). What you really want is not clear from your question, but I hope you can work from the code above to make a working function.

edited Jun 20 '16 at 12:47

answered Jun 20 '16 at 10:29

Patrick

29,357
6
62
90

Thank you. It indeed, solved my problem and exactly what I wanted. If I also want to return the geom of selected streets which are in 50 meters then can it be done by modifying above function or do I have to do write a separate function? – khajlk Jun 20 '16 at 11:42
The problem here is that you are not selecting anything from the `street` table. Or is `st` the same as `street`? What other data do you want to store in table `street`? – Patrick Jun 20 '16 at 12:46
I wanted to store other data such as geometry of selected streets (geom), distance between address to selected streets (helped by you above) and traffic counts w.r.t. address table. Do I have to write separate function for each of them? Yes, ST is street table while ad is address table. – khajlk Jun 20 '16 at 13:59

score 1 · Accepted Answer · edited May 23 '17 at 12:16

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.

Unable to execute the function in plpgsql/postgres

2 Answers2