A question about DDL of Postgres sql script

[rjiang9]

In the sql script:

--postgresql CDM DDL Specification for OMOP Common Data Model 5.4

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE @cdmDatabaseSchema.PERSON (
            person_id integer NOT NULL,

This line: CREATE TABLE @cdmDatabaseSchema.PERSON

How do you run the script? How to deal with @cdmDatabaseSchema

Thanks

[ablack3]

You will have to use SqlRender to replace the parameter.

# connect to an in memory sqlite database
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

sql <- "
CREATE TABLE @cdmDatabaseSchema.PERSON (person_id integer NOT NULL)
"

# replace the parameter(s)
sql <- SqlRender::render(sql, cdmDatabaseSchema = "main")

# translate to the dialect you are using
 sql <- SqlRender::translate(sql, targetDialect = "sqlite")

DBI::dbExecute(con, sql)
#> [1] 0

DBI::dbGetQuery(con, "select * from main.person")
#> [1] person_id
#> <0 rows> (or 0-length row.names)

DBI::dbDisconnect(con)

# Check out using this helper function 
# CommonDataModel::executeDdl()

^{Created on 2022-03-30 by the reprex package (v2.0.1)}

[rjiang9]

I see. Thank you so much for the explanation, Adam! @ablack3

I thought you could just run it as a sql script in query tools in pgAdmin.

[ablack3]

After you replace the parameters (and translate to the postgres sql dialect) you can run the sql from any client.

[clairblacketer]

Thank you @ablack3!

[rjiang9]

got it. @ablack3 thanks.