Temporary tables (dbWriteTable, dbExistsTable)

[gpfr]

Hi,

dbWriteTable does append data into an already created temporary table but it creates an aditional non temporary table with the same name. The reason is than "dbExistsTable" returns false when the table is temporary.
df <- data.frame(id=1:10,value=letters[1:10])
dbExecute(conn,"create temp table tmp_test (id integer, value text)")
dbWriteTable(conn = conn, name = "tmp_test",value = df,row.names=F,append=T)

[danielarantes]

It's probably because the schema of the temp table is different.

Maybe add a check on the dbExistsTable to check if there is a temp table with that name?

[danielarantes]

@tomoakin and @eddelbuettel
Would it be ok to make a pull request to change dbExistsTable to use a query that will check for an existing temp table of that name? It goes to different catalog tables (pg_namespace and pg_class).
That seems to do the trick to use temp tables the way is described above (at least on a pgsql 11).

The query is something like this:

select True from pg_namespace nc JOIN pg_class c ON nc.oid = c.relnamespace
where nc.nspname != 'information_schema' and nc.nspname != 'pg_catalog'
and (
    -- exists in the current schema
    (nc.nspname = 'current_schema_here' and c.relname = 'table_name_here')
    -- or exists in a temp schema as a temp table with the same name
    or (nc.nspname like 'pg_temp%' and c.relname = 'table_name_here' and relpersistence = 't')
)

note: I'm not sure the schema of temp tables is always something that starts with pg_temp. But that's what I've seen. There might be a more secure way to get the temp schema name(s) used in the session that I'm not aware.