OHDSI Home | Forums | Wiki | Github

Problem using OBJECT_ID in rendersql to determine if table exists

Developers
#1

@schuemie,
I’m adding some requested features to CohortExpression builder, and one of the requests to CREATE TABLE on the target table if it does not exist. I added this block of code to my script, but it doesn’t execute on postgres properly:

IF OBJECT_ID('cohort', 'U') IS NULL
  create table cohort
  (
          cohort_definition_id int,
          subject_id bigint,
          cohort_start_date datetime,
          cohort_end_date datetime
  );

Doesn’t work. I overheard a comment that the sql render looks for a pattern of dropping the target table and then creating it, but for my purposes, i will never ever ever (ever ever ever ever ever) drop a table on the user’s behalf. But, i’m willing to try to create the table if it doesn’t exist.

Hence, do we need a new pattern recognizer in sqlrender to detect if a table exists that is cross platform? For the specific postgres I found this query:

SELECT EXISTS (
    SELECT 1 
    FROM   pg_catalog.pg_class c
    JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE  n.nspname = 'schema_name'
    AND    c.relname = 'table_name'
);

Can sqlrender handle this transformation?

-Chris

#2

Hi @Chris_Knoll. I think there’s an easier solution to this problem. PostgreSQL supports CREATE TABLE IF NOT EXISTS syntax and in Oracle we can use a procedure to catch the exception. I’ve added new rules to replacementPatterns.csv, see lines 38, 82, and 120.

There was an issue with RedShift not supporting CREATE TABLE IF NOT EXISTS nor DROP TABLE IF EXISTS, but that should be fixed now as announced here.

@Christian_Reich, note that this is one we also want to support for Netezza.

#3

The part Chris overheard was a discussion regarding using the OBJECT_ID function to determine if a table exists. Not to create it if it doesn’t. The reason we would do this is to determine if a required table is available. I think I’m just going to use the jdbc getTables method since object_id is not supported across all the environments, or at least not through SqlRender in the way it is required.

#4

Ah, ok that is indeed a different use case. I agree the best way forward is probably the JDBC getTables() method. If not, I’ve used these SQL statements in the past:

SQL Server:

SELECT name FROM <database>.sys.tables;

Oracle:

SELECT table_name FROM all_tables WHERE owner='<database.toUpperCase()>';

PostgreSQL:

SELECT table_name FROM information_schema.tables WHERE table_schema = '<database.toLowerCase()>';

It seems preferable that we don’t try to translate these. But getTables() really should do the trick.

t