key_inspector_postgresql quoting issue

[soupofday]

When i try to run describe_tables using key_inspector_postgresql, i get back an error (the table name i am trying to describe in this example is encounter):

describe_tables(db,tableNames,keyInspector = key_inspector_postgresql)

# Warning message:
# In postgresqlQuickSQL(conn, statement, ...) : Could not create execute: 
#    SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
#    FROM   pg_index i
#    JOIN   pg_attribute a ON a.attrelid = i.indrelid
#    AND a.attnum = ANY(i.indkey)
#    WHERE  i.indrelid = "encounter"::regclass
#    AND    i.indisprimary;

When i go to run that SQL statement in postgres directly, the error i get back is column "encounter" does not exist.. In that SQL, encounter is wrapped in double-quotes, so postgres thinks you're asking for a column named "encounter", which of course doesn't exist in pg_index or pg_attribute. If you wrap it further in SINGLE quotes then i think you should be good. I.e. this SQL (note the single quotes wrapping the double quotes wrapping the table name):

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = '"encounter"'::regclass
AND    i.indisprimary

works as expected. And since it's wrapping the table name in double quotes, it should work for any table name, i think. (Although i guess if you pass in double quotes to describe_tables, it will still fail, the double quotes would need to be handled...)

[JohnMount]

First sorry you are having difficulty and thank you very much for taking the time to shead the issue.

Your analysis is correct (thanks!)- I am messing up the value versus column quoting (most databases have notations for both) and generating a bad query. I'll look into that today. The inspectors are meant to be user supplied functions, so if I derive a fixed variation you should be able to use that without even updating the rquery package.

[JohnMount]

I think I have a working (and tested) quoting scheme now.

library("rquery")
driver <- RPostgreSQL::PostgreSQL()
# driver <- RPostgres::Postgres()
my_db <- DBI::dbConnect(driver,
                        host = 'localhost',
                        port = 5432,
                        user = 'johnmount',
                        password = '')

DBI::dbExecute(my_db, "DROP TABLE IF EXISTS weather")

# adapted from https://www.postgresql.org/docs/10/static/tutorial-table.html
DBI::dbExecute(my_db, "
CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date,
   PRIMARY KEY (city, date)
);
")

describe_tables(my_db, "weather", keyInspector = key_inspector_postgresql)
#   tableName isEmpty       indicatorColumn columns       keys colClass
# 1   weather    TRUE table_weather_present         city, date

DBI::dbDisconnect(my_db)