RPostgreSQL - dbExistsTable() is FALSE with schema names?

Tue, Apr 7, 2009 10:46 AM

On Mon, Apr 6, 2009 at 10:41 PM, Sean Davis <sdavis2 at mail.nih.gov> wrote:


On Mon, Apr 6, 2009 at 9:23 PM, Prasenjit Kapat <kapatp at gmail.com> wrote:

On Mon, Apr 6, 2009 at 8:01 PM, Sean Davis <sdavis2 at mail.nih.gov> wrote:


On Mon, Apr 6, 2009 at 7:42 PM, Dirk Eddelbuettel <edd at debian.org>
wrote:

On 6 April 2009 at 19:18, Prasenjit Kapat wrote:
| Hi,
|
| dbExistsTable (conn, name="myschema.mytable") is FALSE, whereas
| dbExistsTable (conn, name="mytable") is TRUE ! 'mytable' is part of
| the 'myschema'.
|
| Is this as expected?

Not sure. Check the Pg API doc of what is expected.

Hmmm, I am not too familiar with postgres myself. I am trying to cover
as much as possible with RPostgreSQL.

And in general, patches and debugging are welcome. ?Sameer, who wrote
RPostgreSQL as part of last year's Google Summer of Code seems to have
lost
touch with the code.

Looks like dbTableExists() method calls dbListTables() method, and that
uses
the SQL query:

select tablename from pg_tables
where schemaname !='information_schema'
and schemaname !='pg_catalog'

This returns the table name, but no schema name.? R code is then used to
check for equality of strings.? This will lead to the incorrect behavior
as
noted.? I can provide a patch, probably, but it won't be immediate, so
if
someone else gets there first, just let us know.

It looks like anything involving schema is buggy, probably the code
was not tested in such scenarios. For example:

dbRemoveTable (con, name="myschema.mytable") is again FALSE and
dbRemoveTable (con, name="mytable") gives (which is expected as
'public.mytable' is nonexistent):

Error in postgresqlExecStatement(conn, statement, ...) :
?RS-DBI driver: (could not Retrieve the result : ERROR: ?table
"mytable" does not exist
)
[1] FALSE

I'll try to see if any proper solution exists.

I was looking through the DBI docs and I didn't see a mention of schema,
specifically.? I'm not very familiar with how other DB drivers deal with the
issue of schema.? Is there a description of how DBI methods are meant to
deal with schema, or is it up to the implementation to deal with the issue
via the ... arguments?

Hopefully someone who knows the details can help on this.

Back here, the following is a quick-n-dirty solution (not a patch of course):

dbExistsTable <- function (con, name, ...)
{
  as.logical (
   dim (
    dbGetQuery (con,
     paste ("select schemaname,tablename from pg_tables where schemaname='",
      rev(strsplit(name, ".", fixed=TRUE)[[1]])[2],
      "' and tablename='",
      rev(strsplit(name, ".", fixed=TRUE)[[1]])[1], "'", sep="")
  )) [1])
}

dbRemoveTable <- function (con, name, ..., cascade=FALSE)
{
  if (dbExistsTable (con, name)) {
    dbGetQuery (con, paste ("drop table ", name, ifelse (cascade, "
cascade", ""), ";", sep=""))
  }
}

Prasenjit

RPostgreSQL - dbExistsTable() is FALSE with schema names?

Thread (6 messages)