[R-sig-DB] RPostgreSQL - dbExistsTable() is FALSE with schema names?

[Prasenjit Kapat]

On Mon, Apr 6, 2009 at 8:01 PM, Sean Davis <sdavis2 using mail.nih.gov> wrote:
>
>
> On Mon, Apr 6, 2009 at 7:42 PM, Dirk Eddelbuettel <edd using 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.

-- 
Prasenjit