INSERTs and UPDATEs in ruby/rails using PostgreSQL functions

Question

I'm using activerecord-postgis-adapter and squeel in a geo oriented application. Fetching data using various queries based on find and where is working fine and squeel allows me to use PostgreSQL/PostGIS functions to query based on spatial functions and datatypes.

However, I can't figure out how to access similar functions when performing INSERTs and UPDATEs. Say I want to do something in the line of:

UPDATE object SET raster = ST_AsRaster(ST_Buffer(ST_Point(1,5),10),150, 150, '2BUI') 
where id = 12345;

How could I perform such a query? Squeel doesn't even seem to have support for other queries than SELECTs.

In a perfect world I would even want to have Rgeo's datatypes automatically converted when used in UPDATE queries, just like with where queries.

I am aware that I can fall back to

ActiveRecord::Base.connection.execute

, but would rather avoid that unless someone tells me it's the only way.

-ra

For something so database specific I would go with ActiveRecord::Base.connection.execute. — fotanus
– fotanus, Commented May 23, 2013 at 19:08

boulder_ruby · Accepted Answer · 2013-09-24 04:59:35Z

1

you could create a connection to the database per model you're working with. one method you can run is the select_value method, there are others:

sql = "UPDATE object SET raster = ST_AsRaster(ST_Buffer(ST_Point(1,5),10),150, 150, '2BUI') where id = 12345;"
conn = ModelName.connection
conn.select_value(sql)

should run your command

see this post by Daniel Azuma for more about this one: http://blog.daniel-azuma.com/archives/216

you could conceivably put this type of code in migrations. In fact its probably a best practice.

answered Sep 24, 2013 at 4:59

boulder_ruby

40k9 gold badges82 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jasper · Accepted Answer · 2022-11-04 14:03:56Z

0

update_all accepts an array param, so that you could e.g. do:

SomeClass
  .where(id: 123)
  .limit(1)
  .update_all(
    ["some_field = some_pg_func(?, ?)", "some", "values"]
  )

https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/Relation.html#method-i-update_all

answered Nov 4, 2022 at 14:03

Jasper

2,57124 silver badges36 bronze badges

Collectives™ on Stack Overflow

INSERTs and UPDATEs in ruby/rails using PostgreSQL functions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related