Arrays are not supported

[GoogleCodeExporter]

Hello,
I am trying to use rpostgres to get native Postgres arrays (i.e. type #
1022 - float8[]) but at no avail:
only pg protocol character array representation is returned, i.e.:

> rs<-dbSendQuery(conn,"select array[1.1,2.1]::float8[] arr;")
Warning message:
In postgresqlExecStatement(conn, statement, ...) :
  RS-DBI driver warning: (unrecognized PostgreSQL field type 1022 in column 0)

 a<-fetch(rs,n=-1)
> a
        arr
1 {1.1,2.1}

> class(a$arr)
[1] "character"



Do you have any plans to add this mapping anytime soon?

Best regards,
Krzysztof

Original issue reported on code.google.com by [email protected] on 10 Jun 2009 at 1:08

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Original comment by [email protected] on 30 Sep 2009 at 5:15

[GoogleCodeExporter]

For interested:
One can create data frame using sth like:
#convert to sth useful, i.e. data frame
suppressWarnings(rs <- dbSendQuery(conn,"select yourarray::float8[] from dual"))
newTS<-fetch(rs,n=-1) 

df<-data.frame(
arr=as.array(strapply(newTS$yourarray,"(([0-9]+)(\\.[0-9]+)*)",as.numeric)),
)

quite slow but works
cheers
Krzysztof

Original comment by [email protected] on 1 Oct 2009 at 4:27

[GoogleCodeExporter]

We are unlikely to have time to provide a patch for this enhancement request, 
but we 
welcome other to contribute a patch.

Original comment by dirk.eddelbuettel on 10 Oct 2009 at 3:55

• Changed state: Accepted
• Added labels: Priority-Low, Type-Enhancement
• Removed labels: Priority-Medium, Type-Defect

[GoogleCodeExporter]

Setting this to 'WontFix' so that it stays open, patches welcome -- we are 
unlikely to 
write them for lack of time.

Original comment by dirk.eddelbuettel on 14 Oct 2009 at 11:14

• Changed state: WontFix

[GoogleCodeExporter]

Change status: just to make it more visible in the issue tracker.

Original comment by [email protected] on 17 Oct 2010 at 9:38

• Changed state: New

[GoogleCodeExporter]

I call R from PostgreSQL now since there's nothing more worthless than a half 
baked ill conceived R interface to a PostgreSQL database. 

Original comment by [email protected] on 17 Oct 2010 at 9:51

[GoogleCodeExporter]

WTF Cinaed.  This is an open project. If you have an itch to scratch, submit a 
patch along with test case that motivates it. 

Original comment by dirk.eddelbuettel on 17 Oct 2010 at 10:28

[GoogleCodeExporter]

Hi,

Has this raised in priority at all on the to-do list? I agree it's an important 
feature, particularly as the prior solution (Rdbi+ RdbiPgSQL) is deprecated.

If not, can one of the authors explain what would have to be done to implement 
this. Please include as much detail as you can/have time for to lower the bar 
for someone to step in and make a stab at submitting a patch.

Since this is (mostly) implemented in RdbiPgSQL, how much of that code can be 
used? The solution in that package returns the array as a string which can then 
be parsed into an array, but clearly a proper R array should be returned. But 
that should be a minor fix.

Cheers,
Demitri

Original comment by [email protected] on 1 Jun 2011 at 8:08

[GoogleCodeExporter]

FYI, Solution with acceptable performance (few ms instead of tens of seconds 
for longer arrays) looks like:

df<-data.frame(
as.array(lapply(strsplit(substr(newTS$vals,2,nchar(newTS$vals)-1),",",fixed=TRUE
),as.numeric))
)

Cheers,
Krzysztof

Original comment by [email protected] on 1 Jun 2011 at 11:07

[GoogleCodeExporter]

If returning the array as a string is sufficient, it is trivial.  Because the 
value is apparently returned as string in current form, just removing the 
warning message upon the defined range of data type. The code is at 
RS_PostgreSQL_createDataMappings
in RS-PostgreSQL.c

Writing codes to properly convert arrays of various possible data types does 
not seem a simple task and PostgreSQL array may be multidimensional and 
complicated even for real numbers.

Original comment by [email protected] on 23 Sep 2011 at 6:38

[GoogleCodeExporter]

FYI: the 'dbColumnInfo' method produces a segfault (Linux R 2.14.1) when I 
query a table that contains an array. A more forgiving result would be 
appreciated, even if arrays are not officially supported.

The simple fix is probably to add the line:
    {"_FLOAT4", 1021},
to 'RS_PostgreSQL_dataTypes[]', but that's clearly not a globally helpful 
solution for the other data types that have not been enumerated in this file. 
I'm writing out of my element a bit here, but perhaps either a catch-all 
'UNKNOWN' data type is defined for all of the otherwise undefined values, or 
the 'pg_type' table could be queried directly when an unknown type is found.
Thanks,  Robert

> dbSendQuery(con, "CREATE TEMPORARY TABLE _a (a REAL[]);")
<PostgreSQLResult:(10147,0,0)> 
> x <- dbSendQuery(con, "SELECT * FROM _a;")
> dbColumnInfo(x)

 *** caught segfault ***
address (nil), cause 'memory not mapped'

Traceback:
 1: .Call("RS_PostgreSQL_typeNames", as.integer(flds$type), PACKAGE = .PostgreSQLPkgName)
 2: postgresqlDescribeFields(res, ...)
 3: is(object, Cl)
 4: is(object, Cl)
 5: .valueClassTest(standardGeneric("dbColumnInfo"), "data.frame",     "dbColumnInfo")
 6: dbColumnInfo(x)

Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace
Selection: 0
Selection: 0
Selection: 3
Warning message:
In postgresqlDescribeFields(res, ...) :
  RS-DBI driver warning: (unknown (1021))

Process R exited abnormally with code 70 at Sat Feb 25 14:32:58 2012

Original comment by [email protected] on 25 Feb 2012 at 7:58

[GoogleCodeExporter]

The segfault is a clear bug. As of r227, RS_DBI_getTypeName() return "UNKNOWN" 
instead of NULL.

Original comment by [email protected] on 27 Feb 2012 at 10:01

[GoogleCodeExporter]

Hi,

I'm looking at this problem again, but am unsure about the data structure that 
could be returned. Let's say I have a PG table like this (and let's ignore 
arrays with greater than one dimension for the moment):

id integer,
values numeric[]

What I retrieve as a result is a data frame, but the values are scalars:

> class(result$id)
[1] "integer"
> class(result$values)
[1] "character"

I have a function that can take the string and convert it to a list:

values2list = function (v) { as.numeric(unlist(strsplit(substring(v, 2, 
nchar(v)-1), ","))) }

(which is basically Krzysztof's solution it looks like) but I can't exactly do 
this:

result$values = values2list(result$values)

I'm happy to try to come up with a patch, but what form would the resulting 
data frame (or something else?) be?

Cheers,
Demitri

Original comment by [email protected] on 1 Mar 2012 at 1:06

[GoogleCodeExporter]

Would anything in PL/R help with this issue? It seems that PL/R can get arrays 
over to R as vectors: http://www.joeconway.com/plr/doc/plr-data.html.

Original comment by [email protected] on 2 Apr 2012 at 2:49

[GoogleCodeExporter]

Further more, postgresqlResultInfo will cause the entire R session crash when 
there is array columns in the resultset.

Could you please fix this, a warning message is better.

Original comment by [email protected] on 16 Nov 2012 at 5:36