Rails - Dec 2005 - Rails and Postgres ARRAYs

Hello list,

I''ve got a Postgres table defined as
create table A (
  id serial primary key,
  names varchar(10)[],
  val float[]
)

Although I *could* split out the names/val arrays into one or more
separate tables with one-to-many relations back to table A, the actual
business problem is such that I''d prefer not to do so if I can avoid
it.

Is there any way of reading/writing individual items in the names &
val fields from Rails?  I''ve tried a few ideas, but haven''t
come up
with anything that works yet.  Using pgsql, I can do
INSERT INTO a (names, vals) VALUES (''{ {''Fred'',
''Bob''}, {1.3, 2.1} }'' );
or
INSERT INTO a (names, vals) VALUES (ARRAY[ [''Fred, Bob]'',
[1.3, 2.1] ]);
and
SELECT names, values FROM A;   ** Returns both entire arrays
SELECT names[1], values[3] FROM A;   ** Returns a specific element
from each array

Has anyone used Postgres arrays with Rails (successfully)?

Regards

Dave M.

monch1962 wrote:
> Hello list,
> 
> I''ve got a Postgres table defined as
> create table A (
>   id serial primary key,
>   names varchar(10)[],
>   val float[]
> )
> 
> Although I *could* split out the names/val arrays into one or more
> separate tables with one-to-many relations back to table A, the actual
> business problem is such that I''d prefer not to do so if I can
avoid
> it.
> 
> Is there any way of reading/writing individual items in the names &
> val fields from Rails?  I''ve tried a few ideas, but
haven''t come up
> with anything that works yet.  Using pgsql, I can do
> INSERT INTO a (names, vals) VALUES (''{ {''Fred'',
''Bob''}, {1.3, 2.1} }'' );
> or
> INSERT INTO a (names, vals) VALUES (ARRAY[ [''Fred, Bob]'',
[1.3, 2.1] ]);
> and
> SELECT names, values FROM A;   ** Returns both entire arrays
> SELECT names[1], values[3] FROM A;   ** Returns a specific element
> from each array
> 
For the selects, you could use find_by_sql like so (in your model):

def get_nth_name( n )
  return self.find_by_sql( "[SELECT names[?] FROM A;", n ] )
end

Of course, this would make your code Postgre specific, but I guess 
you''re willing to do that anyway.  As for inserts, you could override 
the ActiveRecord::Base method(s) pertaining to them.

This is not guaranteed to be an optimal solution as I''m not a Rails 
expert by any means, but it should work.

-- 
Posted via http://www.ruby-forum.com/.