0

As part of my k-medoid algorithm, I have declared a record variable called o_random in my function, which I use to store a random row retrieved from a table of crimes as in this query:

EXECUTE 'SELECT * FROM algorithms.kmedoid_crimes_' ||k||' WHERE  
cluster_id='||k_count||' OFFSET floor(random()*'||row_count||') LIMIT 1'   
INTO o_random;

However, the below query is giving me an error saying "could not identify column "latitude" in record data type". The crimes table I am getting the row from definitely has a field called latitude.. so I'm thinking I'm trying to get the data from the record variable wrongly?

EXECUTE 'UPDATE algorithms.km_cluster_centres_' ||k||'
SET latitude = $1.latitude, longitude = $1.longitude, geom =   
ST_Transform(ST_SetSRID(ST_MakePoint($1.longitude, $1.latitude), 4326),3435)
WHERE id=' ||k_count
USING o_random;

Found some solutions on stack but none of them seem to work.. help would be appreciated.

Ponsietta
  • 315
  • 6
  • 17
  • The solution in the related question applies here too, you can use the double-cast "hack" inside the execute statement too, like `'... latitude = ($1::text::kmedoid_crimes_'||k||').latitude ...'`. Or, if your `kmedoid_crimes_*` tables have an ancestor, or a type, which all tables follow, you can cast the record to that in the USING clause. – pozs Mar 21 '16 at 10:34
  • Hi, you're right it works. Thanks – Ponsietta Mar 21 '16 at 10:50

1 Answers1

0

Just a guess

EXECUTE 'UPDATE algorithms.km_cluster_centres_' || quote_literal(k) ||'
  SET latitude = ($1).latitude, longitude = ($1).longitude, geom =   
  ST_Transform(ST_SetSRID(ST_MakePoint(($1).longitude,($1).latitude), 4326),3435)
  WHERE id=' || quote_literal( k_count )
  USING o_random;

UPDATE: If it dosen't work try to log with http://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html

devanand
  • 5,116
  • 2
  • 20
  • 19
  • nope, same error. It just doesn't seem to find the column names within o_random.. I know that it has data in it though cause I tried outputting it – Ponsietta Mar 21 '16 at 09:06