Think about listing temporary tables about dbi3 HOT 12 OPEN

Yeah that seems reasonable to me

from dbi3.

Handled as part of r-dbi/DBItest#159.

from dbi3.

@jimhester @edgararuiz: Maybe it's more useful to return a third column type in dbListObjects(), with possible values "table", "view" and "temporary"? Or maybe two new columns, "is_view" and "is_temporary"? Do we need to provide filtering options in dbListObjects()?

from dbi3.

There are unfortunately many more table types ODBC data sources can use than table, view and temporary

"TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM", or a data source–specific type name.

https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqltables-function

from dbi3.

Then it would be the backend's job to interpret its own values for the table type and fit it into the categories we provide.?

from dbi3.

That's always a challenge to design, because if we do that @krlmlr , we would need a "catch-all" category for those categories we don't explicitly catalog. I'm thinking of those database types that may have their own, very unique, table designations. This is a long way to say, that if we do, we'll need an "Other" category.

from dbi3.

So maybe "type" (character) and "temporary" (logical), both supporting NA for not yet specified or unknown information?

from dbi3.

dbRemoveTable() needs a temporary argument to make sure the right kind of table is removed with dbWriteTable(overwrite = TRUE).

from dbi3.

Only MySQL/MariaDB seems to support dropping temporary tables via DROP TEMPORARY TABLE. The other two support a temp or pg_temp schema.

from dbi3.

This was difficult for Redshift. Need to revisit.

from dbi3.

Perhaps connections should keep track of temporary tables? Easier to do with a rewrite.

from dbi3.

We can register the temporary tables created through "official" means; it's not perfect but can help for databases that make it hard to enumerate temporary tables.

from dbi3.