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

  • Which RDBMS are you using? – James Anderson May 31 '15 at 17:44
  • Sorry I forgot to state that. I use PostgSQL 9. – Milan Muňko May 31 '15 at 18:37
3

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.

  • 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 – Milan Muňko Jun 2 '15 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. – Daniel Vérité Jun 2 '15 at 21:37
  • Of note, the SQL-standard way (using a correlated subquery) became supported in PostgreSQL 9.5 – Randall Aug 14 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, privacy policy and cookie policy

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