1

I would need to use UPDATE like this:

update offer set (bank, bank_id) = (cost, gid)
from (
select cost, osm_points.gid from pgr_kdijkstraCost(
  'SELECT gid as id, source, target, walk_cost as cost FROM ways',
  offer.vertice_id,
  array(select vertice_id from osm_points where super_type = 'bank'),
  false,false)
join osm_points on id2 = vertice_id
where cost != -1
and osm_points.super_type = 'bank'
order by cost
limit 1)t ;

but I get following:

ERROR: invalid reference to FROM-clause entry for table "offer"

HINT: There is an entry for table "offer", but it cannot be referenced from this part of the query.

Thank you in advance

2
  • Which RDBMS are you using? May 31, 2015 at 17:44
  • Sorry I forgot to state that. I use PostgSQL 9. May 31, 2015 at 18:37

2 Answers 2

5

When using a query of this form: UPDATE a SET columns=values FROM b WHERE ..., the b subquery is evaluated independently of a, and it's the WHERE block at the end of the query that is supposed to join the rows of a and b.

The query in the question attempts to join inside the subquery rather than in a WHERE clause at the uppermost level (in fact it doesn't have such a WHERE clause), but it's not allowed and that's what the error message indicates.

The SQL-standard way of updating each row with a correlated subquery would be:

UPDATE a SET (col1,col2) =
   (SELECT val1,val2 FROM b WHERE expression-needing-values-from-a)

but in older versions of PostgreSQL (9.4 or before), the manual explicitly tells that this form is not implemented. Starting with 9.5, it is implemented.

The typical way to emulate it with postgres 9.4 is to use a copy of table a in the subquery and join back to the updated table in the upper level's WHERE clause, like this:

 UPDATE a SET (col1,col2)=(s.col1,s.col2)
  FROM (SELECT a2.ctid,col1,col2 AS ctid_of_row FROM b, a AS a2 WHERE...) AS s
   WHERE s.ctid_of_row = a.ctid;

knowing that ctid is the system pseudo-column that indicates the physical location of the row version within its table. Alternatively, the primary key can be used if there is one.

In your case, the transformation is not obvious because your subquery is meant to produce only one row with its ORDER BY... LIMIT 1 at the end. It should be redesigned to produce all the target rows in a single resultset, presumably (verticeid,cost,gid) where cost and gid are determined per offer.verticeid.

3
  • Probem is that I need to pass value of vertice_id from current row to the function pgr_kdjistracost this function returns multiple records and I need one with minimal cost Jun 2, 2015 at 8:30
  • @Milan: you can write something along the lines of select vertice_id, (select ... from pgr_kdjistracost(...) join... where ... order by cost limit 1) as s from offer. Jun 2, 2015 at 21:37
  • Of note, the SQL-standard way (using a correlated subquery) became supported in PostgreSQL 9.5
    – Randall
    Aug 14, 2019 at 15:55
0

After some googling and @Daniel's suggestions I came to following solution I need define my own type and use it to extract data from subquery:

create type closest_poi as (cost float, poi_gid bigint);
with closest as (
select
  distinct o.gid,
  ((select row(cost,gid)
  from pgr_kdijkstraCost(
    'SELECT gid as id, source, target, walk_cost as cost FROM ways',
    o.vertice_id,
    array(select vertice_id
          from osm_points
          where super_type = 'kindergarden'),
    false,false)
  join osm_points on id2 = vertice_id
  where cost != -1
  and super_type = 'kindergarden'
  order by cost
  limit 1)::text::closest_poi).*
from offer o, osm_points p
where p.super_type = 'kindergarden'
and st_dwithin(o.the_geom::geography,p.the_geom::geography,5000)
order by 1)

update offer 
set 
  kindergarden = closest.cost,
  kindergarden_id = closest.poi_gid
from closest
where offer.gid = closest.gid;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.