Re: Arrays with Rails? - Mailing list pgsql-general

On Fri, 13 Apr 2007 10:30:29 +0200, Tino Wildenhain <tino@wildenhain.de>
wrote:

> Joshua D. Drake schrieb:
>> Rick Schumeyer wrote:
>>> Has anyone here used a postgres array with Rails?  If so, how?
>>>
>>  split()?
>
> Err... there is no type mapping?


    You know, some languages spoil us developers, so that we for granted that
Doing It The Right Way is indeed the way it is, and then we feel the
burning pain of having to deal with the reality...
    For instance, python and psycopg massively spoil you :

cursor.execute( "select '{1,2}'::INTEGER[] AS myarray, 1 AS myint, 'abc'
AS mytext, %s as myarg1, %s as myarg2, %s as myarg3", (
    u"this is an unicode string €éàù",
    [1, 2, 3],
    ["this","is","an","array","of","text"]
))

for row in cursor:
    for name, item in row.items():
        print name, type( item ), item

    Results :
    As you can see, arguments and results are auto-converted from python
types to postgres types (you can write converters for your own types).
This includes unicode, arrays, etc.
    The interface is clean and elegant : you provide SQL with %s in it and a
list of arguments. You can use named or positional arguments too.

mytext <type 'str'> abc
myarray <type 'list'> [1, 2]
myint <type 'int'> 1
myarg1 <type 'str'> this is an unicode string €éàù
myarg2 <type 'list'> [1, 2, 3]
myarg3 <type 'list'> ['this', 'is', 'an', 'array', 'of', 'text']

    Some (like me) would argue that this is NORMAL and that anything inferior
to this is murder. I believe the job of libraries and languages is to help
the programmer, not torture him.

    Then, other languages will make you feel the pain of having to quote all
your arguments YOURSELF and provide all results as string.
    The most famous offender is PHP (this causes countless security holes).
    Ruby is no better :

require "postgres"
conn = PGconn.connect("localhost", 5432, "", "", "test")
res = conn.exec("select '{1,2}'::INTEGER[] AS myarray, 1 AS myint, 'abc'
AS mytext;")
res.result.each {|row|
    row.each { |item|
        puts item
        puts item.class
        puts "\n"
    }
}

{1,2}
String

1
String

abc
String

    So the answer to your question is :

    - either suffer
    - or code an additional layer above the ruby postgres lib which does the
same as the python lib above.
    I partially did this for PHP. It's a lifesaver. No more addslashes() !
Yay !