CODEWITHSUNDEEP
postgresqlsql-insertplpgsqlset-returning-functionssql-returning
khajlk
khajlk

Reputation: 851

Unable to execute the function in plpgsql/postgres

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

Upvotes: 1

Views: 1550

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656381

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:

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:

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.

Upvotes: 1

Patrick
Patrick

Reputation: 32179

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.

Upvotes: 1

Related Questions