routing network function issue

Question

Following on from a this question, I have some dynamic SQL I'm running successfully in PL/PgSQL with EXECUTE, but it doesn't seem to have the intended effect.

The following code is part of a plpgsql function that I have created. This intends to go to each point that is lying on the same track and for each point find its nearest point in order to eventually I will create a network between all the tracks. For some reson it is not working properly.

The i represent the number of the current track.

 DECLARE
    _r record;
    i int := 0;
    source_geom character varying;
    target_geom character varying;

BEGIN

WHILE i < 3 --DEPENDS ON THE NUMBER OF TRACKS
LOOP

FOR _r IN EXECUTE ' SELECT a.'|| quote_ident(gid_cname) || ' AS id,'
|| ' st_astext( a.'||quote_ident(geo_cname)||') AS source,' 
|| ' st_astext(b.'||quote_ident(geo_cname)||') AS target, ' 
|| ' ST_Distance(a.'||quote_ident(geo_cname) || ' ,  b.'||quote_ident(geo_cname)||') As dist_ft'
|| ' FROM ' || quote_ident (geom_table) ||' AS a INNER JOIN ' || quote_ident(geom_table) || ' As b ON ST_DWithin(a.'||quote_ident(geo_cname)|| ', b.'||quote_ident(geo_cname)|| ',1000)'
|| ' WHERE b.'||quote_ident(gid_cname)|| ' > a.'||quote_ident(gid_cname)|| ' AND b.'||quote_ident(tid_cname)|| ' = '||i|| 'AND a.'||quote_ident(tid_cname)|| ' = '||i|| 
   ' ORDER BY dist_ft '
|| ' Limit 1 '

LOOP

source_geom := _r.source;
target_geom := _r.target;


EXECUTE 'update ' || quote_ident(geom_table) || 
    ' SET source = ''' || source_geom ||''' 
    , target = ''' || target_geom || ''' 
    WHERE ' || quote_ident(gid_cname) || ' =  ' || _r.id;
END LOOP;

i = i + 1;

END LOOP;

RETURN 'OK';
END;

The thing is that I have tested the query in plain sql and it worked brilliantly. Now for some reason in plpgsql it is not working properly. Below you can find the same query in plain sql.

SELECT a.ogc_fid AS id, st_astext( a.wkb_geometry) AS source, st_astext(b.wkb_geometry) AS target, ST_Distance(a.wkb_geometry, b.wkb_geometry) As dist_ft
FROM track_points AS a INNER JOIN track_points As b
ON ST_DWithin(a.wkb_geometry , b.wkb_geometry, 1000)
WHERE b.ogc_fid > a.ogc_fid AND b.track_fid = 0 AND a.track_fid = 0 
order by dist_ft

When you say *not working properly* what *exactly* do you mean? Error? No results? Blows up your cat? — Craig Ringer, Oct 22 '12 at 10:25
it means that it gives only few results rather than the full result as expected and not all corrected. — IT_info, Oct 22 '12 at 10:57

score 1 · Answer 1 · edited May 23 '17 at 10:24

1

Rather than ||, quote_literal quote_ident try using format() and EXECUTE ... USING.

See this recent answer.

The usual cause of queries failing in PL/PgSQL when they work in plain SQL is name clashes with variables declared in PL/PgSQL, but that doesn't apply to dynamic EXECUTE queries because they cannot references variables declared in the PL/PgSQL functin.

In this case I think it's more likely that you made a mistake when converting it to a dynamic query.

Try replacing:

EXECUTE 'SELECT ...'

with:

query_text = 'SELECT ...'
RAISE NOTICE 'About to run: %',query_text;
EXECUTE query_text;

where query_text is DECLAREd as text earlier. See if the query text you're generating really matches the query you run by hand.

It's much easier to debug dynamic SQL if it's better formatted. Try something more like:

EXECUTE format(
  $query$
    SELECT a.%1$I AS id,
           st_astext(a.%2$I) AS source,
           st_astext(b.%2$I) AS target,
           ST_Distance(a.%2$I, b.%2$I) AS dist_ft
    FROM %3$I AS a INNER JOIN %3$I As b
      ON ST_DWithin(a.%2$I , b.%2$I, 1000)
    WHERE b.%1$I > a.%1$I 
      AND b.%4$I = 0 AND a.%4$I = 0 
    ORDER BY dist_ft
    LIMIT %5$L
  $query$,
  gid_cname, geo_cname, geom_table, tid_cname, 1
);

There's no need for a USING clause as no literal parameters are passed.

edited May 23 '17 at 10:24

Community

1
1

answered Oct 22 '12 at 10:04

Craig Ringer

307,061
76
688
778

Yes yes I have already seen that answer. Infect, thanks for the suggestions. – IT_info Oct 22 '12 at 10:08
But, do have any idea why is not working properly in plpgsql? because I have been trying to make it work for hours. Or else can you explain me exactly the FOR _r IN EXECUTE how it works? – IT_info Oct 22 '12 at 10:11
@Ryan Ah, that was your question earlier. A lot of questions come through so it's easy to forget, especially when it isn't the same code. Would've helped if you'd mentioned it / linked to it. As I said, the chances are you made an error when converting your function to dynamic SQL; try the suggestion in the updated answer. – Craig Ringer Oct 22 '12 at 10:17
@Ryan Also make sure it is, in fact, not working; it could be that the `UPDATE`s are in fact the problem, so the outer loop query works but you never see any results. Again, add `RAISE NOTICE` lines to verify. – Craig Ringer Oct 22 '12 at 10:22
1

@Ryan All `FOR _r IN EXECUTE` does is execute the query text and for each row returned by the query store that row in the record variable `_r` then execute the loop. – Craig Ringer Oct 22 '12 at 10:59

score 0 · Answer 2 · answered Nov 02 '12 at 10:41

0

I see a very small difference: LIMIT 1. If you would omit that from the dynamic SQL, would that solve it?

With kind regards,

Loek

answered Nov 02 '12 at 10:41

Loek Bergman

2,192
20
18

routing network function issue

2 Answers2