Hi I have a problem accessing an array field in a Postgresql database.
Here is the table definition.
View "neil.flashing_codes"
Column | Type | Modifiers
-------------+-----------------------+-----------
code | character varying(10) |
description | text |
folds | integer[] |
View definition:
SELECT flashings.code, max(flashings.description::text) AS
description, array_accum(flashings.folds) AS folds
FROM flashings
GROUP BY flashings.code;
Here is a sample of the records.
select * from flashing_codes limit 10;
code | description | folds
----------+------------------------------------------+---------------------
SG.05A | ALUMINIUM FLASHING C/B GIRTH 520MM FOLDS | {1,0,2,3,4,5,6}
DN.03Z | .6MM FLASHING Z/A GIRTH 200MM FOLDS | {0,6,5,4,3,2,1}
SR.05G | .6MM FLASHING GAL GIRTH 300MM FOLDS | {5,4,3,2,1,0,6}
RR.06Z | .6MM FLASHING Z/A GIRTH 400MM FOLDS | {6,5,4,3,2,1,0}
RR.05G | .6MM FLASHING GAL GIRTH 300MM FOLDS | {6,5,4,3,2,1,0}
RR.09C | .6MM FLASHING C/B GIRTH 600MM FOLDS | {6,5,4,3,2,1,0}
BS.09C | .6MM FLASHING C/B GIRTH 600MM FOLDS | {0,1,2,3,4,5,6,7,8}
DN.TAPER | TAPPER CUTTING AND FOLDING SURCHARGE | {}
ST.13G | .6MM FLASHING GAL 601-800MM FOLDS | {0,1,2,3,4,5,6}
SR.06C | .6MM FLASHING C/B GIRTH 400MM FOLDS | {0,6,5,4,3,2,1}
(10 rows)
In the view as part of the debug I put this
<%=h @flashing.inspect() %>
which gave this result.
#<FlashingCode:0xb78791b8
@attributes={"code"=>"SR.05G",
"description"=>".6MM FLASHING GAL GIRTH 300MM FOLDS",
"folds"=>"{5,4,3,2,1,0,6}"}>
Here is what the problem is this
<%=h @flashing.folds.inspect() if @flashing %>
only gives this as a result.
0
I can''t get the data from the folds array. I have no trouble getting
any of the other fields in this view, or any other table/view I have setup.
Can anyone give me an idea what''s going on?
Regards Neil.
On 2/19/06, Neil Dugan <ruby@butterflystitches.com.au> wrote:> Hi I have a problem accessing an array field in a Postgresql database. > > -------------+-----------------------+----------- > code | character varying(10) | > description | text | > folds | integer[] | > > select * from flashing_codes limit 10; > code | description | folds > ----------+------------------------------------------+--------------------- > SG.05A | ALUMINIUM FLASHING C/B GIRTH 520MM FOLDS | {1,0,2,3,4,5,6} > > In the view as part of the debug I put this > <%=h @flashing.inspect() %> > which gave this result. > #<FlashingCode:0xb78791b8 @attributes={"code"=>"SR.05G", > "description"=>".6MM FLASHING GAL GIRTH 300MM FOLDS", > "folds"=>"{5,4,3,2,1,0,6}"}> > > Here is what the problem is this > <%=h @flashing.folds.inspect() if @flashing %> > only gives this as a result. > 0 > > I can''t get the data from the folds array. I have no trouble getting > any of the other fields in this view, or any other table/view I have setup. > > Can anyone give me an idea what''s going on? > > Regards Neil.http://dev.rubyonrails.org/ticket/3272 PostgreSQLAdapter isn''t aware of array types, and translate_field_type returns incorrect results, because of wrong assumption, that only size constraints can follow column type name, so when you access model field - @flashing.folds - Column#type_cast tries to parse this value it as integer, so you receive 0. Btw, what returns @flashing[:folds] ? To fix it add a check if column type is array and treat them as strings, ("fixing" code for ticket #3272 is a bit incorrect, but following should work ok) in activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, around line 449: def translate_field_type(field_type) # Match the beginning of field_type since it may have a size constraint on the end. case field_type when /\[\]$/ then ''string'' # XXX as arrays aren''t supported, treat them as strings when /^timestamp/i then ''datetime'' ------------------- This fix will give you string representation of your array, but to access and modify elements in it you''ll have to parse it yourself - define own getter/setter or extend Column#type_cast and Quoting#quote to handle arrays. HTH
Lugovoi Nikolai wrote:> On 2/19/06, Neil Dugan <ruby@butterflystitches.com.au> wrote: > >>Hi I have a problem accessing an array field in a Postgresql database. >> >>-------------+-----------------------+----------- >> code | character varying(10) | >> description | text | >> folds | integer[] | >> >>select * from flashing_codes limit 10; >> code | description | folds >>----------+------------------------------------------+--------------------- >> SG.05A | ALUMINIUM FLASHING C/B GIRTH 520MM FOLDS | {1,0,2,3,4,5,6} >> >>In the view as part of the debug I put this >><%=h @flashing.inspect() %> >>which gave this result. >>#<FlashingCode:0xb78791b8 @attributes={"code"=>"SR.05G", >>"description"=>".6MM FLASHING GAL GIRTH 300MM FOLDS", >>"folds"=>"{5,4,3,2,1,0,6}"}> >> >>Here is what the problem is this >><%=h @flashing.folds.inspect() if @flashing %> >>only gives this as a result. >>0 >> >>I can''t get the data from the folds array. I have no trouble getting >>any of the other fields in this view, or any other table/view I have setup. >> >>Can anyone give me an idea what''s going on? >> >>Regards Neil. > > > http://dev.rubyonrails.org/ticket/3272 > > PostgreSQLAdapter isn''t aware of array types, and translate_field_type > returns incorrect results, because of wrong assumption, that only size > constraints can follow column type name, so when you access model > field - @flashing.folds - Column#type_cast tries to parse this value > it as integer, so you receive 0. Btw, what returns @flashing[:folds] ? > > To fix it add a check if column type is array and treat them as strings, > ("fixing" code for ticket #3272 is a bit incorrect, but following > should work ok) > in activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, > around line 449: > > def translate_field_type(field_type) > # Match the beginning of field_type since it may have a size > constraint on the end. > case field_type > when /\[\]$/ then ''string'' # XXX as arrays aren''t > supported, treat them as strings > when /^timestamp/i then ''datetime'' > ------------------- > > This fix will give you string representation of your array, but to > access and modify elements in it you''ll have to parse it yourself - > define own getter/setter or extend Column#type_cast and Quoting#quote > to handle arrays.that worked to fix the problem. I used the @flashing[:folds].delete(''{}'').split('','') to convert to an array. could something like this be put in into the postgresql_adapter.rb file somewhere? Maybe as its constructing up the hash for output.> > HTH >