R/m4_addInfo.R
In yikeshu0611/mimicR: MIMIC database with R
Defines functions
addInfo_4
Documented in
addInfo_4
#' add functions, schema and tables
#' create shema mimic4 and 27 tables
#' @param conn connection, if missing, it will be get 'connect_MIMIC' from global environment.
#'
#' @return shema mimic4 and 27 tables in PostgreSQL.
#' @export
#'
#' @examples
#' \donttest{
#' addInfo_4()
#' }
addInfo_4 <- function(user='postgres',password = 'pg'){
con <- DBI::dbConnect(drv = RPostgreSQL::PostgreSQL(),
user = user,
password = password)
conn <- dbplyr::src_dbi(con, auto_disconnect = TRUE)
# create database
datname <- as.data.frame(dplyr::tbl(conn,dbplyr::sql('SELECT datname FROM pg_database')))[,1]
if (! 'mimic' %in% tolower(datname)){
message('\ncreate database mimic')
DBI::dbGetQuery(conn = conn$con,
statement = "CREATE DATABASE mimic;")
}
con <- DBI::dbConnect(drv = RPostgreSQL::PostgreSQL(),
user = user,
password = password,
dbname='mimic')
conn <- dbplyr::src_dbi(con, auto_disconnect = TRUE)
# add schema -------
message('\nadd schema mimic4')
DBI::dbGetQuery(conn = conn$con,
statement = "CREATE SCHEMA IF NOT EXISTS mimic4;")
message('\nadd 27 tables names and columns')
# 27 tables ------------------------------
admissions<- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS admissions;CREATE TABLE admissions( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, admittime TIMESTAMP NOT NULL, dischtime TIMESTAMP, deathtime TIMESTAMP, admission_type VARCHAR(40) NOT NULL, admission_location VARCHAR(60), discharge_location VARCHAR(60), insurance VARCHAR(255), language VARCHAR(10), marital_status VARCHAR(30), ethnicity VARCHAR(80), edregtime TIMESTAMP, edouttime TIMESTAMP, hospital_expire_flag SMALLINT);"
patients <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS patients;CREATE TABLE patients( subject_id INTEGER NOT NULL, gender CHAR(1) NOT NULL, anchor_age SMALLINT, anchor_year SMALLINT NOT NULL, anchor_year_group VARCHAR(20) NOT NULL, dod DATE);"
transfers <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS transfers;CREATE TABLE transfers( subject_id INTEGER NOT NULL, hadm_id INTEGER, transfer_id INTEGER NOT NULL, eventtype VARCHAR(10), careunit VARCHAR(255), intime TIMESTAMP, outtime TIMESTAMP);"
d_hcpcs <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS d_hcpcs;CREATE TABLE d_hcpcs( code CHAR(5) NOT NULL, category SMALLINT, long_description TEXT, short_description VARCHAR(180));"
diagnoses_icd <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS diagnoses_icd;CREATE TABLE diagnoses_icd( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, seq_num INTEGER NOT NULL, icd_code CHAR(7), icd_version SMALLINT);"
d_icd_diagnoses <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS d_icd_diagnoses;CREATE TABLE d_icd_diagnoses( icd_code CHAR(7) NOT NULL, icd_version SMALLINT NOT NULL, long_title VARCHAR(255));"
d_icd_procedures <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS d_icd_procedures;CREATE TABLE d_icd_procedures( icd_code CHAR(7) NOT NULL, icd_version SMALLINT NOT NULL, long_title VARCHAR(222));"
d_labitems <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS d_labitems;CREATE TABLE d_labitems( itemid INTEGER NOT NULL, label VARCHAR(50), fluid VARCHAR(50), category VARCHAR(50), loinc_code VARCHAR(50));"
drgcodes <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS drgcodes;CREATE TABLE drgcodes( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, drg_type VARCHAR(4), drg_code VARCHAR(10) NOT NULL, description VARCHAR(195), drg_severity SMALLINT, drg_mortality SMALLINT);"
emar_detail <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS emar_detail;CREATE TABLE emar_detail( subject_id INTEGER NOT NULL, emar_id VARCHAR(25) NOT NULL, emar_seq INTEGER NOT NULL, parent_field_ordinal NUMERIC(3, 2), administration_type VARCHAR(50), pharmacy_id INTEGER, barcode_type VARCHAR(4), reason_for_no_barcode TEXT, complete_dose_not_given VARCHAR(5), dose_due VARCHAR(100), dose_due_unit VARCHAR(50), dose_given VARCHAR(255), dose_given_unit VARCHAR(50), will_remainder_of_dose_be_given VARCHAR(5), product_amount_given VARCHAR(30), product_unit VARCHAR(30), product_code VARCHAR(30), product_description VARCHAR(255), product_description_other VARCHAR(255), prior_infusion_rate VARCHAR(20), infusion_rate VARCHAR(20), infusion_rate_adjustment VARCHAR(50), infusion_rate_adjustment_amount VARCHAR(30), infusion_rate_unit VARCHAR(30), route VARCHAR(10), infusion_complete VARCHAR(1), completion_interval VARCHAR(30), new_iv_bag_hung VARCHAR(1), continued_infusion_in_other_location VARCHAR(1), restart_interval VARCHAR(2305), side VARCHAR(10), site VARCHAR(255), non_formulary_visual_verification VARCHAR(1));"
emar <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS emar;CREATE TABLE emar( subject_id INTEGER NOT NULL, hadm_id INTEGER, emar_id VARCHAR(25) NOT NULL, emar_seq INTEGER NOT NULL, poe_id VARCHAR(25) NOT NULL, pharmacy_id INTEGER, charttime TIMESTAMP NOT NULL, medication TEXT, event_txt VARCHAR(100), scheduletime TIMESTAMP, storetime TIMESTAMP NOT NULL);"
hcpcsevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS hcpcsevents;CREATE TABLE hcpcsevents( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, chartdate TIMESTAMP(0) NOT NULL, hcpcs_cd CHAR(5) NOT NULL, seq_num INTEGER NOT NULL, short_description VARCHAR(180));"
labevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS labevents;CREATE TABLE labevents( labevent_id INTEGER NOT NULL, subject_id INTEGER NOT NULL, hadm_id INTEGER, specimen_id INTEGER NOT NULL, itemid INTEGER NOT NULL, charttime TIMESTAMP(0), storetime TIMESTAMP(0), value VARCHAR(200), valuenum DOUBLE PRECISION, valueuom VARCHAR(20), ref_range_lower DOUBLE PRECISION, ref_range_upper DOUBLE PRECISION, flag VARCHAR(10), priority VARCHAR(7), comments TEXT);"
microbiologyevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS microbiologyevents;CREATE TABLE microbiologyevents( microevent_id INTEGER NOT NULL, subject_id INTEGER NOT NULL, hadm_id INTEGER, micro_specimen_id INTEGER NOT NULL, chartdate TIMESTAMP(0) NOT NULL, charttime TIMESTAMP(0), spec_itemid INTEGER NOT NULL, spec_type_desc VARCHAR(100) NOT NULL, test_seq INTEGER NOT NULL, storedate TIMESTAMP(0), storetime TIMESTAMP(0), test_itemid INTEGER, test_name VARCHAR(100), org_itemid INTEGER, org_name VARCHAR(100), isolate_num SMALLINT, quantity VARCHAR(50), ab_itemid INTEGER, ab_name VARCHAR(30), dilution_text VARCHAR(10), dilution_comparison VARCHAR(20), dilution_value DOUBLE PRECISION, interpretation VARCHAR(5), comments TEXT);"
pharmacy <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS pharmacy;CREATE TABLE pharmacy( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, pharmacy_id INTEGER NOT NULL, poe_id VARCHAR(25), starttime TIMESTAMP(3), stoptime TIMESTAMP(3), medication TEXT, proc_type VARCHAR(50) NOT NULL, status VARCHAR(50), entertime TIMESTAMP(3) NOT NULL, verifiedtime TIMESTAMP(3), route VARCHAR(50), frequency VARCHAR(50), disp_sched VARCHAR(255), infusion_type VARCHAR(15), sliding_scale VARCHAR(1), lockout_interval VARCHAR(50), basal_rate REAL, one_hr_max VARCHAR(10), doses_per_24_hrs REAL, duration REAL, duration_interval VARCHAR(50), expiration_value INTEGER, expiration_unit VARCHAR(50), expirationdate TIMESTAMP(3), dispensation VARCHAR(50), fill_quantity VARCHAR(50));"
poe_detail <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS poe_detail;CREATE TABLE poe_detail( poe_id VARCHAR(25) NOT NULL, poe_seq INTEGER NOT NULL, subject_id INTEGER NOT NULL, field_name VARCHAR(255) NOT NULL, field_value TEXT);"
poe <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS poe;CREATE TABLE poe( poe_id VARCHAR(25) NOT NULL, poe_seq INTEGER NOT NULL, subject_id INTEGER NOT NULL, hadm_id INTEGER, ordertime TIMESTAMP(0) NOT NULL, order_type VARCHAR(25) NOT NULL, order_subtype VARCHAR(50), transaction_type VARCHAR(15), discontinue_of_poe_id VARCHAR(25), discontinued_by_poe_id VARCHAR(25), order_status VARCHAR(15));"
prescriptions <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS prescriptions;CREATE TABLE prescriptions( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, pharmacy_id INTEGER NOT NULL, starttime TIMESTAMP(3), stoptime TIMESTAMP(3), drug_type VARCHAR(20) NOT NULL, drug VARCHAR(255) NOT NULL, gsn VARCHAR(255), ndc VARCHAR(25), prod_strength VARCHAR(255), form_rx VARCHAR(25), dose_val_rx VARCHAR(100), dose_unit_rx VARCHAR(50), form_val_disp VARCHAR(50), form_unit_disp VARCHAR(50), doses_per_24_hrs REAL, route VARCHAR(50));"
procedures_icd <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS procedures_icd;CREATE TABLE procedures_icd( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, seq_num INTEGER NOT NULL, chartdate TIMESTAMP(0) NOT NULL, icd_code VARCHAR(7), icd_version SMALLINT);"
services <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS services;CREATE TABLE services( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, transfertime TIMESTAMP NOT NULL, prev_service VARCHAR(10), curr_service VARCHAR(10));"
chartevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS chartevents;CREATE TABLE chartevents( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, stay_id INTEGER NOT NULL, charttime TIMESTAMP NOT NULL, storetime TIMESTAMP, itemid INTEGER NOT NULL, value VARCHAR(200), valuenum FLOAT, valueuom VARCHAR(20), warning SMALLINT);"
datetimeevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS datetimeevents;CREATE TABLE datetimeevents( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, stay_id INTEGER NOT NULL, charttime TIMESTAMP NOT NULL, storetime TIMESTAMP, itemid INTEGER NOT NULL, value TIMESTAMP NOT NULL, valueuom VARCHAR(20), warning SMALLINT);"
d_items <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS d_items;CREATE TABLE d_items( itemid INTEGER NOT NULL, label VARCHAR(100) NOT NULL, abbreviation VARCHAR(50) NOT NULL, linksto VARCHAR(30) NOT NULL, category VARCHAR(50) NOT NULL, unitname VARCHAR(50), param_type VARCHAR(20) NOT NULL, lownormalvalue FLOAT, highnormalvalue FLOAT);"
icustays <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS icustays;CREATE TABLE icustays( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, stay_id INTEGER NOT NULL, first_careunit VARCHAR(255), last_careunit VARCHAR(255), intime TIMESTAMP, outtime TIMESTAMP, los FLOAT);"
inputevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS inputevents;CREATE TABLE inputevents( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, stay_id INTEGER, starttime TIMESTAMP NOT NULL, endtime TIMESTAMP NOT NULL, storetime TIMESTAMP, itemid INTEGER NOT NULL, amount FLOAT, amountuom VARCHAR(20), rate FLOAT, rateuom VARCHAR(20), orderid INTEGER NOT NULL, linkorderid INTEGER, ordercategoryname VARCHAR(50), secondaryordercategoryname VARCHAR(50), ordercomponenttypedescription VARCHAR(100), ordercategorydescription VARCHAR(30), patientweight FLOAT, totalamount FLOAT, totalamountuom VARCHAR(50), isopenbag SMALLINT, continueinnextdept SMALLINT, cancelreason SMALLINT, statusdescription VARCHAR(20), originalamount FLOAT, originalrate FLOAT);"
outputevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS outputevents;CREATE TABLE outputevents( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, stay_id INTEGER NOT NULL, charttime TIMESTAMP(3) NOT NULL, storetime TIMESTAMP(3) NOT NULL, itemid INTEGER NOT NULL, value FLOAT NOT NULL, valueuom VARCHAR(20));"
procedureevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS procedureevents;CREATE TABLE procedureevents( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, stay_id INTEGER NOT NULL, starttime TIMESTAMP NOT NULL, endtime TIMESTAMP NOT NULL, storetime TIMESTAMP NOT NULL, itemid INTEGER NOT NULL, value FLOAT, valueuom VARCHAR(20), location VARCHAR(100), locationcategory VARCHAR(50), orderid INTEGER, linkorderid INTEGER, ordercategoryname VARCHAR(50), secondaryordercategoryname VARCHAR(50), ordercategorydescription VARCHAR(30), patientweight FLOAT, totalamount FLOAT, totalamountuom VARCHAR(50), isopenbag SMALLINT, continueinnextdept SMALLINT, cancelreason SMALLINT, statusdescription VARCHAR(20), comments_date TIMESTAMP, ORIGINALAMOUNT FLOAT, ORIGINALRATE FLOAT);"
xname = c('admissions', 'chartevents', 'd_hcpcs', 'd_icd_diagnoses', 'd_icd_procedures', 'd_items', 'd_labitems', 'datetimeevents', 'diagnoses_icd', 'drgcodes', 'emar', 'emar_detail', 'hcpcsevents', 'icustays', 'inputevents', 'labevents', 'microbiologyevents', 'outputevents', 'patients', 'pharmacy', 'poe', 'poe_detail', 'prescriptions', 'procedureevents', 'procedures_icd', 'services', 'transfers')
x=c(admissions, chartevents, d_hcpcs, d_icd_diagnoses, d_icd_procedures, d_items, d_labitems, datetimeevents, diagnoses_icd, drgcodes, emar, emar_detail, hcpcsevents, icustays, inputevents, labevents, microbiologyevents, outputevents, patients, pharmacy, poe, poe_detail, prescriptions, procedureevents, procedures_icd, services, transfers)
for (i in 1:length(x)) {
cat(" table",paste0(i,'. ',xname[i],'\n'))
DBI::dbGetQuery(conn = conn$con,
statement = x[i])
}
message('\nadd 13 functions')
bigquery_format_to_psql <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.bigquery_format_to_psql(character varying)\n\n-- DROP FUNCTION mimic4.bigquery_format_to_psql(character varying);\n\nCREATE OR REPLACE FUNCTION mimic4.bigquery_format_to_psql(\n\tformat_str character varying)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN \n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n format_str\n , '%S', 'SS'\n )\n , '%M', 'MI'\n )\n , '%H', 'HH24'\n )\n , '%d', 'dd'\n )\n , '%m', 'mm'\n )\n , '%Y', 'yyyy'\n )\n;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.bigquery_format_to_psql(character varying)\n OWNER TO postgres;\n"
datetime1 <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime(date)\n\n-- DROP FUNCTION mimic4.datetime(date);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime(\n\tdt date)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN CAST(dt AS TIMESTAMP(3));\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime(date)\n OWNER TO postgres;\n"
datetime2 <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime(integer, integer, integer, integer, integer, integer)\n\n-- DROP FUNCTION mimic4.datetime(integer, integer, integer, integer, integer, integer);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime(\n\tyear integer,\n\tmonth integer,\n\tday integer,\n\thour integer,\n\tminute integer,\n\tsecond integer)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_TIMESTAMP(\n TO_CHAR(year, '0000') || TO_CHAR(month, '00') || TO_CHAR(day, '00') || TO_CHAR(hour, '00') || TO_CHAR(minute, '00') || TO_CHAR(second, '00'),\n 'yyyymmddHH24MISS'\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime(integer, integer, integer, integer, integer, integer)\n OWNER TO postgres;\n"
datetime_add <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime_add(timestamp without time zone, interval)\n\n-- DROP FUNCTION mimic4.datetime_add(timestamp without time zone, interval);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime_add(\n\tdatetime_val timestamp without time zone,\n\tintvl interval)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN datetime_val + intvl;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime_add(timestamp without time zone, interval)\n OWNER TO postgres;\n"
datetime_diff <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime_diff(timestamp without time zone, timestamp without time zone, text)\n\n-- DROP FUNCTION mimic4.datetime_diff(timestamp without time zone, timestamp without time zone, text);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime_diff(\n\tendtime timestamp without time zone,\n\tstarttime timestamp without time zone,\n\tdatepart text)\n RETURNS numeric\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN \n EXTRACT(EPOCH FROM endtime - starttime) /\n CASE\n WHEN datepart = 'SECOND' THEN 1.0\n WHEN datepart = 'MINUTE' THEN 60.0\n WHEN datepart = 'HOUR' THEN 3600.0\n WHEN datepart = 'DAY' THEN 24*3600.0\n WHEN datepart = 'YEAR' THEN 365.242*24*3600.0\n ELSE NULL END;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime_diff(timestamp without time zone, timestamp without time zone, text)\n OWNER TO postgres;\n"
datetime_sub <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime_sub(timestamp without time zone, interval)\n\n-- DROP FUNCTION mimic4.datetime_sub(timestamp without time zone, interval);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime_sub(\n\tdatetime_val timestamp without time zone,\n\tintvl interval)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN datetime_val - intvl;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime_sub(timestamp without time zone, interval)\n OWNER TO postgres;\n"
format_date <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.format_date(character varying, timestamp without time zone)\n\n-- DROP FUNCTION mimic4.format_date(character varying, timestamp without time zone);\n\nCREATE OR REPLACE FUNCTION mimic4.format_date(\n\tformat_str character varying,\n\tdatetime_val timestamp without time zone)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_CHAR(\n datetime_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.format_date(character varying, timestamp without time zone)\n OWNER TO postgres;\n"
format_datetime <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.format_datetime(character varying, timestamp without time zone)\n\n-- DROP FUNCTION mimic4.format_datetime(character varying, timestamp without time zone);\n\nCREATE OR REPLACE FUNCTION mimic4.format_datetime(\n\tformat_str character varying,\n\tdatetime_val timestamp without time zone)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_CHAR(\n datetime_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.format_datetime(character varying, timestamp without time zone)\n OWNER TO postgres;\n"
generate_array <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.generate_array(integer, integer)\n\n-- DROP FUNCTION mimic4.generate_array(integer, integer);\n\nCREATE OR REPLACE FUNCTION mimic4.generate_array(\n\ti integer,\n\tj integer)\n RETURNS SETOF integer \n LANGUAGE 'sql'\n COST 100\n VOLATILE PARALLEL UNSAFE\n ROWS 1000\n\nAS $BODY$\nSELECT GENERATE_SERIES(i, j)\n$BODY$;\n\nALTER FUNCTION mimic4.generate_array(integer, integer)\n OWNER TO postgres;\n"
parse_date <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.parse_date(character varying, character varying)\n\n-- DROP FUNCTION mimic4.parse_date(character varying, character varying);\n\nCREATE OR REPLACE FUNCTION mimic4.parse_date(\n\tformat_str character varying,\n\tstring_val character varying)\n RETURNS date\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_DATE(\n string_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.parse_date(character varying, character varying)\n OWNER TO postgres;\n"
parse_datetime <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.parse_datetime(character varying, character varying)\n\n-- DROP FUNCTION mimic4.parse_datetime(character varying, character varying);\n\nCREATE OR REPLACE FUNCTION mimic4.parse_datetime(\n\tformat_str character varying,\n\tstring_val character varying)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_TIMESTAMP(\n string_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.parse_datetime(character varying, character varying)\n OWNER TO postgres;\n"
regexp_contains <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.regexp_contains(text, text)\n\n-- DROP FUNCTION mimic4.regexp_contains(text, text);\n\nCREATE OR REPLACE FUNCTION mimic4.regexp_contains(\n\tstr text,\n\tpattern text)\n RETURNS boolean\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN str ~ pattern;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.regexp_contains(text, text)\n OWNER TO postgres;\n"
regexp_extract <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.regexp_extract(text, text)\n\n-- DROP FUNCTION mimic4.regexp_extract(text, text);\n\nCREATE OR REPLACE FUNCTION mimic4.regexp_extract(\n\tstr text,\n\tpattern text)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN substring(str from pattern);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.regexp_extract(text, text)\n OWNER TO postgres;\n"
xname = c("bigquery_format_to_psql", "datetime_add", "datetime_diff",
"datetime_sub", "datetime1", "datetime2", "format_date", "format_datetime",
"generate_array", "parse_date", "parse_datetime", "regexp_contains",
"regexp_extract")
x=c(bigquery_format_to_psql, datetime_add, datetime_diff,
datetime_sub, datetime1, datetime2, format_date, format_datetime,
generate_array, parse_date, parse_datetime,
regexp_contains, regexp_extract)
for (i in 1:length(x)) {
cat(" add",paste0(i,'. ',xname[i],'\n'))
DBI::dbGetQuery(conn = conn$con,
statement = x[i])
}
message('\nDONE !!! ')
}
# 13 functions ------------------------------------------
# message('\nadd 13 functions')
# bigquery_format_to_psql <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.bigquery_format_to_psql(character varying)\n\n-- DROP FUNCTION mimic4.bigquery_format_to_psql(character varying);\n\nCREATE OR REPLACE FUNCTION mimic4.bigquery_format_to_psql(\n\tformat_str character varying)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN \n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n format_str\n , '%S', 'SS'\n )\n , '%M', 'MI'\n )\n , '%H', 'HH24'\n )\n , '%d', 'dd'\n )\n , '%m', 'mm'\n )\n , '%Y', 'yyyy'\n )\n;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.bigquery_format_to_psql(character varying)\n OWNER TO postgres;\n"
# datetime1 <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime(date)\n\n-- DROP FUNCTION mimic4.datetime(date);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime(\n\tdt date)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN CAST(dt AS TIMESTAMP(3));\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime(date)\n OWNER TO postgres;\n"
# datetime2 <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime(integer, integer, integer, integer, integer, integer)\n\n-- DROP FUNCTION mimic4.datetime(integer, integer, integer, integer, integer, integer);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime(\n\tyear integer,\n\tmonth integer,\n\tday integer,\n\thour integer,\n\tminute integer,\n\tsecond integer)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_TIMESTAMP(\n TO_CHAR(year, '0000') || TO_CHAR(month, '00') || TO_CHAR(day, '00') || TO_CHAR(hour, '00') || TO_CHAR(minute, '00') || TO_CHAR(second, '00'),\n 'yyyymmddHH24MISS'\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime(integer, integer, integer, integer, integer, integer)\n OWNER TO postgres;\n"
# datetime_add <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime_add(timestamp without time zone, interval)\n\n-- DROP FUNCTION mimic4.datetime_add(timestamp without time zone, interval);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime_add(\n\tdatetime_val timestamp without time zone,\n\tintvl interval)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN datetime_val + intvl;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime_add(timestamp without time zone, interval)\n OWNER TO postgres;\n"
# datetime_diff <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime_diff(timestamp without time zone, timestamp without time zone, text)\n\n-- DROP FUNCTION mimic4.datetime_diff(timestamp without time zone, timestamp without time zone, text);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime_diff(\n\tendtime timestamp without time zone,\n\tstarttime timestamp without time zone,\n\tdatepart text)\n RETURNS numeric\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN \n EXTRACT(EPOCH FROM endtime - starttime) /\n CASE\n WHEN datepart = 'SECOND' THEN 1.0\n WHEN datepart = 'MINUTE' THEN 60.0\n WHEN datepart = 'HOUR' THEN 3600.0\n WHEN datepart = 'DAY' THEN 24*3600.0\n WHEN datepart = 'YEAR' THEN 365.242*24*3600.0\n ELSE NULL END;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime_diff(timestamp without time zone, timestamp without time zone, text)\n OWNER TO postgres;\n"
# datetime_sub <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime_sub(timestamp without time zone, interval)\n\n-- DROP FUNCTION mimic4.datetime_sub(timestamp without time zone, interval);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime_sub(\n\tdatetime_val timestamp without time zone,\n\tintvl interval)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN datetime_val - intvl;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime_sub(timestamp without time zone, interval)\n OWNER TO postgres;\n"
# format_date <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.format_date(character varying, timestamp without time zone)\n\n-- DROP FUNCTION mimic4.format_date(character varying, timestamp without time zone);\n\nCREATE OR REPLACE FUNCTION mimic4.format_date(\n\tformat_str character varying,\n\tdatetime_val timestamp without time zone)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_CHAR(\n datetime_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.format_date(character varying, timestamp without time zone)\n OWNER TO postgres;\n"
# format_datetime <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.format_datetime(character varying, timestamp without time zone)\n\n-- DROP FUNCTION mimic4.format_datetime(character varying, timestamp without time zone);\n\nCREATE OR REPLACE FUNCTION mimic4.format_datetime(\n\tformat_str character varying,\n\tdatetime_val timestamp without time zone)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_CHAR(\n datetime_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.format_datetime(character varying, timestamp without time zone)\n OWNER TO postgres;\n"
# generate_array <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.generate_array(integer, integer)\n\n-- DROP FUNCTION mimic4.generate_array(integer, integer);\n\nCREATE OR REPLACE FUNCTION mimic4.generate_array(\n\ti integer,\n\tj integer)\n RETURNS SETOF integer \n LANGUAGE 'sql'\n COST 100\n VOLATILE PARALLEL UNSAFE\n ROWS 1000\n\nAS $BODY$\nSELECT GENERATE_SERIES(i, j)\n$BODY$;\n\nALTER FUNCTION mimic4.generate_array(integer, integer)\n OWNER TO postgres;\n"
# parse_date <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.parse_date(character varying, character varying)\n\n-- DROP FUNCTION mimic4.parse_date(character varying, character varying);\n\nCREATE OR REPLACE FUNCTION mimic4.parse_date(\n\tformat_str character varying,\n\tstring_val character varying)\n RETURNS date\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_DATE(\n string_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.parse_date(character varying, character varying)\n OWNER TO postgres;\n"
# parse_datetime <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.parse_datetime(character varying, character varying)\n\n-- DROP FUNCTION mimic4.parse_datetime(character varying, character varying);\n\nCREATE OR REPLACE FUNCTION mimic4.parse_datetime(\n\tformat_str character varying,\n\tstring_val character varying)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_TIMESTAMP(\n string_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.parse_datetime(character varying, character varying)\n OWNER TO postgres;\n"
# regexp_contains <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.regexp_contains(text, text)\n\n-- DROP FUNCTION mimic4.regexp_contains(text, text);\n\nCREATE OR REPLACE FUNCTION mimic4.regexp_contains(\n\tstr text,\n\tpattern text)\n RETURNS boolean\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN str ~ pattern;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.regexp_contains(text, text)\n OWNER TO postgres;\n"
# regexp_extract <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.regexp_extract(text, text)\n\n-- DROP FUNCTION mimic4.regexp_extract(text, text);\n\nCREATE OR REPLACE FUNCTION mimic4.regexp_extract(\n\tstr text,\n\tpattern text)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN substring(str from pattern);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.regexp_extract(text, text)\n OWNER TO postgres;\n"
# xname = c("bigquery_format_to_psql", "datetime_add", "datetime_diff",
# "datetime_sub", "datetime1", "datetime2", "format_date", "format_datetime",
# "generate_array", "parse_date", "parse_datetime", "regexp_contains",
# "regexp_extract")
# x=c(bigquery_format_to_psql, datetime_add, datetime_diff,
# datetime_sub, datetime1, datetime2, format_date, format_datetime,
# generate_array, parse_date, parse_datetime,
# regexp_contains, regexp_extract)
# for (i in 1:length(x)) {
# cat(" add",paste0(i,'. ',xname[i],'\n'))
# DBI::dbGetQuery(conn = conn$con,
# statement = x[i])
# }
yikeshu0611/mimicR documentation built on Dec. 23, 2021, 7:21 p.m.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Defines functions addInfo_4
Documented in addInfo_4
#' add functions, schema and tables
#' create shema mimic4 and 27 tables
#' @param conn connection, if missing, it will be get 'connect_MIMIC' from global environment.
#'
#' @return shema mimic4 and 27 tables in PostgreSQL.
#' @export
#'
#' @examples
#' \donttest{
#' addInfo_4()
#' }
addInfo_4 <- function(user='postgres',password = 'pg'){
con <- DBI::dbConnect(drv = RPostgreSQL::PostgreSQL(),
user = user,
password = password)
conn <- dbplyr::src_dbi(con, auto_disconnect = TRUE)
# create database
datname <- as.data.frame(dplyr::tbl(conn,dbplyr::sql('SELECT datname FROM pg_database')))[,1]
if (! 'mimic' %in% tolower(datname)){
message('\ncreate database mimic')
DBI::dbGetQuery(conn = conn$con,
statement = "CREATE DATABASE mimic;")
}
con <- DBI::dbConnect(drv = RPostgreSQL::PostgreSQL(),
user = user,
password = password,
dbname='mimic')
conn <- dbplyr::src_dbi(con, auto_disconnect = TRUE)
# add schema -------
message('\nadd schema mimic4')
DBI::dbGetQuery(conn = conn$con,
statement = "CREATE SCHEMA IF NOT EXISTS mimic4;")
message('\nadd 27 tables names and columns')
# 27 tables ------------------------------
admissions<- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS admissions;CREATE TABLE admissions( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, admittime TIMESTAMP NOT NULL, dischtime TIMESTAMP, deathtime TIMESTAMP, admission_type VARCHAR(40) NOT NULL, admission_location VARCHAR(60), discharge_location VARCHAR(60), insurance VARCHAR(255), language VARCHAR(10), marital_status VARCHAR(30), ethnicity VARCHAR(80), edregtime TIMESTAMP, edouttime TIMESTAMP, hospital_expire_flag SMALLINT);"
patients <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS patients;CREATE TABLE patients( subject_id INTEGER NOT NULL, gender CHAR(1) NOT NULL, anchor_age SMALLINT, anchor_year SMALLINT NOT NULL, anchor_year_group VARCHAR(20) NOT NULL, dod DATE);"
transfers <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS transfers;CREATE TABLE transfers( subject_id INTEGER NOT NULL, hadm_id INTEGER, transfer_id INTEGER NOT NULL, eventtype VARCHAR(10), careunit VARCHAR(255), intime TIMESTAMP, outtime TIMESTAMP);"
d_hcpcs <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS d_hcpcs;CREATE TABLE d_hcpcs( code CHAR(5) NOT NULL, category SMALLINT, long_description TEXT, short_description VARCHAR(180));"
diagnoses_icd <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS diagnoses_icd;CREATE TABLE diagnoses_icd( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, seq_num INTEGER NOT NULL, icd_code CHAR(7), icd_version SMALLINT);"
d_icd_diagnoses <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS d_icd_diagnoses;CREATE TABLE d_icd_diagnoses( icd_code CHAR(7) NOT NULL, icd_version SMALLINT NOT NULL, long_title VARCHAR(255));"
d_icd_procedures <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS d_icd_procedures;CREATE TABLE d_icd_procedures( icd_code CHAR(7) NOT NULL, icd_version SMALLINT NOT NULL, long_title VARCHAR(222));"
d_labitems <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS d_labitems;CREATE TABLE d_labitems( itemid INTEGER NOT NULL, label VARCHAR(50), fluid VARCHAR(50), category VARCHAR(50), loinc_code VARCHAR(50));"
drgcodes <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS drgcodes;CREATE TABLE drgcodes( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, drg_type VARCHAR(4), drg_code VARCHAR(10) NOT NULL, description VARCHAR(195), drg_severity SMALLINT, drg_mortality SMALLINT);"
emar_detail <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS emar_detail;CREATE TABLE emar_detail( subject_id INTEGER NOT NULL, emar_id VARCHAR(25) NOT NULL, emar_seq INTEGER NOT NULL, parent_field_ordinal NUMERIC(3, 2), administration_type VARCHAR(50), pharmacy_id INTEGER, barcode_type VARCHAR(4), reason_for_no_barcode TEXT, complete_dose_not_given VARCHAR(5), dose_due VARCHAR(100), dose_due_unit VARCHAR(50), dose_given VARCHAR(255), dose_given_unit VARCHAR(50), will_remainder_of_dose_be_given VARCHAR(5), product_amount_given VARCHAR(30), product_unit VARCHAR(30), product_code VARCHAR(30), product_description VARCHAR(255), product_description_other VARCHAR(255), prior_infusion_rate VARCHAR(20), infusion_rate VARCHAR(20), infusion_rate_adjustment VARCHAR(50), infusion_rate_adjustment_amount VARCHAR(30), infusion_rate_unit VARCHAR(30), route VARCHAR(10), infusion_complete VARCHAR(1), completion_interval VARCHAR(30), new_iv_bag_hung VARCHAR(1), continued_infusion_in_other_location VARCHAR(1), restart_interval VARCHAR(2305), side VARCHAR(10), site VARCHAR(255), non_formulary_visual_verification VARCHAR(1));"
emar <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS emar;CREATE TABLE emar( subject_id INTEGER NOT NULL, hadm_id INTEGER, emar_id VARCHAR(25) NOT NULL, emar_seq INTEGER NOT NULL, poe_id VARCHAR(25) NOT NULL, pharmacy_id INTEGER, charttime TIMESTAMP NOT NULL, medication TEXT, event_txt VARCHAR(100), scheduletime TIMESTAMP, storetime TIMESTAMP NOT NULL);"
hcpcsevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS hcpcsevents;CREATE TABLE hcpcsevents( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, chartdate TIMESTAMP(0) NOT NULL, hcpcs_cd CHAR(5) NOT NULL, seq_num INTEGER NOT NULL, short_description VARCHAR(180));"
labevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS labevents;CREATE TABLE labevents( labevent_id INTEGER NOT NULL, subject_id INTEGER NOT NULL, hadm_id INTEGER, specimen_id INTEGER NOT NULL, itemid INTEGER NOT NULL, charttime TIMESTAMP(0), storetime TIMESTAMP(0), value VARCHAR(200), valuenum DOUBLE PRECISION, valueuom VARCHAR(20), ref_range_lower DOUBLE PRECISION, ref_range_upper DOUBLE PRECISION, flag VARCHAR(10), priority VARCHAR(7), comments TEXT);"
microbiologyevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS microbiologyevents;CREATE TABLE microbiologyevents( microevent_id INTEGER NOT NULL, subject_id INTEGER NOT NULL, hadm_id INTEGER, micro_specimen_id INTEGER NOT NULL, chartdate TIMESTAMP(0) NOT NULL, charttime TIMESTAMP(0), spec_itemid INTEGER NOT NULL, spec_type_desc VARCHAR(100) NOT NULL, test_seq INTEGER NOT NULL, storedate TIMESTAMP(0), storetime TIMESTAMP(0), test_itemid INTEGER, test_name VARCHAR(100), org_itemid INTEGER, org_name VARCHAR(100), isolate_num SMALLINT, quantity VARCHAR(50), ab_itemid INTEGER, ab_name VARCHAR(30), dilution_text VARCHAR(10), dilution_comparison VARCHAR(20), dilution_value DOUBLE PRECISION, interpretation VARCHAR(5), comments TEXT);"
pharmacy <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS pharmacy;CREATE TABLE pharmacy( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, pharmacy_id INTEGER NOT NULL, poe_id VARCHAR(25), starttime TIMESTAMP(3), stoptime TIMESTAMP(3), medication TEXT, proc_type VARCHAR(50) NOT NULL, status VARCHAR(50), entertime TIMESTAMP(3) NOT NULL, verifiedtime TIMESTAMP(3), route VARCHAR(50), frequency VARCHAR(50), disp_sched VARCHAR(255), infusion_type VARCHAR(15), sliding_scale VARCHAR(1), lockout_interval VARCHAR(50), basal_rate REAL, one_hr_max VARCHAR(10), doses_per_24_hrs REAL, duration REAL, duration_interval VARCHAR(50), expiration_value INTEGER, expiration_unit VARCHAR(50), expirationdate TIMESTAMP(3), dispensation VARCHAR(50), fill_quantity VARCHAR(50));"
poe_detail <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS poe_detail;CREATE TABLE poe_detail( poe_id VARCHAR(25) NOT NULL, poe_seq INTEGER NOT NULL, subject_id INTEGER NOT NULL, field_name VARCHAR(255) NOT NULL, field_value TEXT);"
poe <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS poe;CREATE TABLE poe( poe_id VARCHAR(25) NOT NULL, poe_seq INTEGER NOT NULL, subject_id INTEGER NOT NULL, hadm_id INTEGER, ordertime TIMESTAMP(0) NOT NULL, order_type VARCHAR(25) NOT NULL, order_subtype VARCHAR(50), transaction_type VARCHAR(15), discontinue_of_poe_id VARCHAR(25), discontinued_by_poe_id VARCHAR(25), order_status VARCHAR(15));"
prescriptions <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS prescriptions;CREATE TABLE prescriptions( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, pharmacy_id INTEGER NOT NULL, starttime TIMESTAMP(3), stoptime TIMESTAMP(3), drug_type VARCHAR(20) NOT NULL, drug VARCHAR(255) NOT NULL, gsn VARCHAR(255), ndc VARCHAR(25), prod_strength VARCHAR(255), form_rx VARCHAR(25), dose_val_rx VARCHAR(100), dose_unit_rx VARCHAR(50), form_val_disp VARCHAR(50), form_unit_disp VARCHAR(50), doses_per_24_hrs REAL, route VARCHAR(50));"
procedures_icd <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS procedures_icd;CREATE TABLE procedures_icd( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, seq_num INTEGER NOT NULL, chartdate TIMESTAMP(0) NOT NULL, icd_code VARCHAR(7), icd_version SMALLINT);"
services <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS services;CREATE TABLE services( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, transfertime TIMESTAMP NOT NULL, prev_service VARCHAR(10), curr_service VARCHAR(10));"
chartevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS chartevents;CREATE TABLE chartevents( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, stay_id INTEGER NOT NULL, charttime TIMESTAMP NOT NULL, storetime TIMESTAMP, itemid INTEGER NOT NULL, value VARCHAR(200), valuenum FLOAT, valueuom VARCHAR(20), warning SMALLINT);"
datetimeevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS datetimeevents;CREATE TABLE datetimeevents( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, stay_id INTEGER NOT NULL, charttime TIMESTAMP NOT NULL, storetime TIMESTAMP, itemid INTEGER NOT NULL, value TIMESTAMP NOT NULL, valueuom VARCHAR(20), warning SMALLINT);"
d_items <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS d_items;CREATE TABLE d_items( itemid INTEGER NOT NULL, label VARCHAR(100) NOT NULL, abbreviation VARCHAR(50) NOT NULL, linksto VARCHAR(30) NOT NULL, category VARCHAR(50) NOT NULL, unitname VARCHAR(50), param_type VARCHAR(20) NOT NULL, lownormalvalue FLOAT, highnormalvalue FLOAT);"
icustays <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS icustays;CREATE TABLE icustays( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, stay_id INTEGER NOT NULL, first_careunit VARCHAR(255), last_careunit VARCHAR(255), intime TIMESTAMP, outtime TIMESTAMP, los FLOAT);"
inputevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS inputevents;CREATE TABLE inputevents( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, stay_id INTEGER, starttime TIMESTAMP NOT NULL, endtime TIMESTAMP NOT NULL, storetime TIMESTAMP, itemid INTEGER NOT NULL, amount FLOAT, amountuom VARCHAR(20), rate FLOAT, rateuom VARCHAR(20), orderid INTEGER NOT NULL, linkorderid INTEGER, ordercategoryname VARCHAR(50), secondaryordercategoryname VARCHAR(50), ordercomponenttypedescription VARCHAR(100), ordercategorydescription VARCHAR(30), patientweight FLOAT, totalamount FLOAT, totalamountuom VARCHAR(50), isopenbag SMALLINT, continueinnextdept SMALLINT, cancelreason SMALLINT, statusdescription VARCHAR(20), originalamount FLOAT, originalrate FLOAT);"
outputevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS outputevents;CREATE TABLE outputevents( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, stay_id INTEGER NOT NULL, charttime TIMESTAMP(3) NOT NULL, storetime TIMESTAMP(3) NOT NULL, itemid INTEGER NOT NULL, value FLOAT NOT NULL, valueuom VARCHAR(20));"
procedureevents <- "SET client_min_messages TO WARNING;\nset search_path to mimic4;\nDROP TABLE IF EXISTS procedureevents;CREATE TABLE procedureevents( subject_id INTEGER NOT NULL, hadm_id INTEGER NOT NULL, stay_id INTEGER NOT NULL, starttime TIMESTAMP NOT NULL, endtime TIMESTAMP NOT NULL, storetime TIMESTAMP NOT NULL, itemid INTEGER NOT NULL, value FLOAT, valueuom VARCHAR(20), location VARCHAR(100), locationcategory VARCHAR(50), orderid INTEGER, linkorderid INTEGER, ordercategoryname VARCHAR(50), secondaryordercategoryname VARCHAR(50), ordercategorydescription VARCHAR(30), patientweight FLOAT, totalamount FLOAT, totalamountuom VARCHAR(50), isopenbag SMALLINT, continueinnextdept SMALLINT, cancelreason SMALLINT, statusdescription VARCHAR(20), comments_date TIMESTAMP, ORIGINALAMOUNT FLOAT, ORIGINALRATE FLOAT);"
xname = c('admissions', 'chartevents', 'd_hcpcs', 'd_icd_diagnoses', 'd_icd_procedures', 'd_items', 'd_labitems', 'datetimeevents', 'diagnoses_icd', 'drgcodes', 'emar', 'emar_detail', 'hcpcsevents', 'icustays', 'inputevents', 'labevents', 'microbiologyevents', 'outputevents', 'patients', 'pharmacy', 'poe', 'poe_detail', 'prescriptions', 'procedureevents', 'procedures_icd', 'services', 'transfers')
x=c(admissions, chartevents, d_hcpcs, d_icd_diagnoses, d_icd_procedures, d_items, d_labitems, datetimeevents, diagnoses_icd, drgcodes, emar, emar_detail, hcpcsevents, icustays, inputevents, labevents, microbiologyevents, outputevents, patients, pharmacy, poe, poe_detail, prescriptions, procedureevents, procedures_icd, services, transfers)
for (i in 1:length(x)) {
cat(" table",paste0(i,'. ',xname[i],'\n'))
DBI::dbGetQuery(conn = conn$con,
statement = x[i])
}
message('\nadd 13 functions')
bigquery_format_to_psql <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.bigquery_format_to_psql(character varying)\n\n-- DROP FUNCTION mimic4.bigquery_format_to_psql(character varying);\n\nCREATE OR REPLACE FUNCTION mimic4.bigquery_format_to_psql(\n\tformat_str character varying)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN \n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n format_str\n , '%S', 'SS'\n )\n , '%M', 'MI'\n )\n , '%H', 'HH24'\n )\n , '%d', 'dd'\n )\n , '%m', 'mm'\n )\n , '%Y', 'yyyy'\n )\n;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.bigquery_format_to_psql(character varying)\n OWNER TO postgres;\n"
datetime1 <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime(date)\n\n-- DROP FUNCTION mimic4.datetime(date);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime(\n\tdt date)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN CAST(dt AS TIMESTAMP(3));\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime(date)\n OWNER TO postgres;\n"
datetime2 <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime(integer, integer, integer, integer, integer, integer)\n\n-- DROP FUNCTION mimic4.datetime(integer, integer, integer, integer, integer, integer);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime(\n\tyear integer,\n\tmonth integer,\n\tday integer,\n\thour integer,\n\tminute integer,\n\tsecond integer)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_TIMESTAMP(\n TO_CHAR(year, '0000') || TO_CHAR(month, '00') || TO_CHAR(day, '00') || TO_CHAR(hour, '00') || TO_CHAR(minute, '00') || TO_CHAR(second, '00'),\n 'yyyymmddHH24MISS'\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime(integer, integer, integer, integer, integer, integer)\n OWNER TO postgres;\n"
datetime_add <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime_add(timestamp without time zone, interval)\n\n-- DROP FUNCTION mimic4.datetime_add(timestamp without time zone, interval);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime_add(\n\tdatetime_val timestamp without time zone,\n\tintvl interval)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN datetime_val + intvl;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime_add(timestamp without time zone, interval)\n OWNER TO postgres;\n"
datetime_diff <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime_diff(timestamp without time zone, timestamp without time zone, text)\n\n-- DROP FUNCTION mimic4.datetime_diff(timestamp without time zone, timestamp without time zone, text);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime_diff(\n\tendtime timestamp without time zone,\n\tstarttime timestamp without time zone,\n\tdatepart text)\n RETURNS numeric\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN \n EXTRACT(EPOCH FROM endtime - starttime) /\n CASE\n WHEN datepart = 'SECOND' THEN 1.0\n WHEN datepart = 'MINUTE' THEN 60.0\n WHEN datepart = 'HOUR' THEN 3600.0\n WHEN datepart = 'DAY' THEN 24*3600.0\n WHEN datepart = 'YEAR' THEN 365.242*24*3600.0\n ELSE NULL END;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime_diff(timestamp without time zone, timestamp without time zone, text)\n OWNER TO postgres;\n"
datetime_sub <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime_sub(timestamp without time zone, interval)\n\n-- DROP FUNCTION mimic4.datetime_sub(timestamp without time zone, interval);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime_sub(\n\tdatetime_val timestamp without time zone,\n\tintvl interval)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN datetime_val - intvl;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime_sub(timestamp without time zone, interval)\n OWNER TO postgres;\n"
format_date <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.format_date(character varying, timestamp without time zone)\n\n-- DROP FUNCTION mimic4.format_date(character varying, timestamp without time zone);\n\nCREATE OR REPLACE FUNCTION mimic4.format_date(\n\tformat_str character varying,\n\tdatetime_val timestamp without time zone)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_CHAR(\n datetime_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.format_date(character varying, timestamp without time zone)\n OWNER TO postgres;\n"
format_datetime <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.format_datetime(character varying, timestamp without time zone)\n\n-- DROP FUNCTION mimic4.format_datetime(character varying, timestamp without time zone);\n\nCREATE OR REPLACE FUNCTION mimic4.format_datetime(\n\tformat_str character varying,\n\tdatetime_val timestamp without time zone)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_CHAR(\n datetime_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.format_datetime(character varying, timestamp without time zone)\n OWNER TO postgres;\n"
generate_array <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.generate_array(integer, integer)\n\n-- DROP FUNCTION mimic4.generate_array(integer, integer);\n\nCREATE OR REPLACE FUNCTION mimic4.generate_array(\n\ti integer,\n\tj integer)\n RETURNS SETOF integer \n LANGUAGE 'sql'\n COST 100\n VOLATILE PARALLEL UNSAFE\n ROWS 1000\n\nAS $BODY$\nSELECT GENERATE_SERIES(i, j)\n$BODY$;\n\nALTER FUNCTION mimic4.generate_array(integer, integer)\n OWNER TO postgres;\n"
parse_date <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.parse_date(character varying, character varying)\n\n-- DROP FUNCTION mimic4.parse_date(character varying, character varying);\n\nCREATE OR REPLACE FUNCTION mimic4.parse_date(\n\tformat_str character varying,\n\tstring_val character varying)\n RETURNS date\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_DATE(\n string_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.parse_date(character varying, character varying)\n OWNER TO postgres;\n"
parse_datetime <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.parse_datetime(character varying, character varying)\n\n-- DROP FUNCTION mimic4.parse_datetime(character varying, character varying);\n\nCREATE OR REPLACE FUNCTION mimic4.parse_datetime(\n\tformat_str character varying,\n\tstring_val character varying)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_TIMESTAMP(\n string_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.parse_datetime(character varying, character varying)\n OWNER TO postgres;\n"
regexp_contains <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.regexp_contains(text, text)\n\n-- DROP FUNCTION mimic4.regexp_contains(text, text);\n\nCREATE OR REPLACE FUNCTION mimic4.regexp_contains(\n\tstr text,\n\tpattern text)\n RETURNS boolean\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN str ~ pattern;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.regexp_contains(text, text)\n OWNER TO postgres;\n"
regexp_extract <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.regexp_extract(text, text)\n\n-- DROP FUNCTION mimic4.regexp_extract(text, text);\n\nCREATE OR REPLACE FUNCTION mimic4.regexp_extract(\n\tstr text,\n\tpattern text)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN substring(str from pattern);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.regexp_extract(text, text)\n OWNER TO postgres;\n"
xname = c("bigquery_format_to_psql", "datetime_add", "datetime_diff",
"datetime_sub", "datetime1", "datetime2", "format_date", "format_datetime",
"generate_array", "parse_date", "parse_datetime", "regexp_contains",
"regexp_extract")
x=c(bigquery_format_to_psql, datetime_add, datetime_diff,
datetime_sub, datetime1, datetime2, format_date, format_datetime,
generate_array, parse_date, parse_datetime,
regexp_contains, regexp_extract)
for (i in 1:length(x)) {
cat(" add",paste0(i,'. ',xname[i],'\n'))
DBI::dbGetQuery(conn = conn$con,
statement = x[i])
}
message('\nDONE !!! ')
}
# 13 functions ------------------------------------------
# message('\nadd 13 functions')
# bigquery_format_to_psql <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.bigquery_format_to_psql(character varying)\n\n-- DROP FUNCTION mimic4.bigquery_format_to_psql(character varying);\n\nCREATE OR REPLACE FUNCTION mimic4.bigquery_format_to_psql(\n\tformat_str character varying)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN \n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n format_str\n , '%S', 'SS'\n )\n , '%M', 'MI'\n )\n , '%H', 'HH24'\n )\n , '%d', 'dd'\n )\n , '%m', 'mm'\n )\n , '%Y', 'yyyy'\n )\n;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.bigquery_format_to_psql(character varying)\n OWNER TO postgres;\n"
# datetime1 <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime(date)\n\n-- DROP FUNCTION mimic4.datetime(date);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime(\n\tdt date)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN CAST(dt AS TIMESTAMP(3));\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime(date)\n OWNER TO postgres;\n"
# datetime2 <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime(integer, integer, integer, integer, integer, integer)\n\n-- DROP FUNCTION mimic4.datetime(integer, integer, integer, integer, integer, integer);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime(\n\tyear integer,\n\tmonth integer,\n\tday integer,\n\thour integer,\n\tminute integer,\n\tsecond integer)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_TIMESTAMP(\n TO_CHAR(year, '0000') || TO_CHAR(month, '00') || TO_CHAR(day, '00') || TO_CHAR(hour, '00') || TO_CHAR(minute, '00') || TO_CHAR(second, '00'),\n 'yyyymmddHH24MISS'\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime(integer, integer, integer, integer, integer, integer)\n OWNER TO postgres;\n"
# datetime_add <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime_add(timestamp without time zone, interval)\n\n-- DROP FUNCTION mimic4.datetime_add(timestamp without time zone, interval);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime_add(\n\tdatetime_val timestamp without time zone,\n\tintvl interval)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN datetime_val + intvl;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime_add(timestamp without time zone, interval)\n OWNER TO postgres;\n"
# datetime_diff <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime_diff(timestamp without time zone, timestamp without time zone, text)\n\n-- DROP FUNCTION mimic4.datetime_diff(timestamp without time zone, timestamp without time zone, text);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime_diff(\n\tendtime timestamp without time zone,\n\tstarttime timestamp without time zone,\n\tdatepart text)\n RETURNS numeric\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN \n EXTRACT(EPOCH FROM endtime - starttime) /\n CASE\n WHEN datepart = 'SECOND' THEN 1.0\n WHEN datepart = 'MINUTE' THEN 60.0\n WHEN datepart = 'HOUR' THEN 3600.0\n WHEN datepart = 'DAY' THEN 24*3600.0\n WHEN datepart = 'YEAR' THEN 365.242*24*3600.0\n ELSE NULL END;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime_diff(timestamp without time zone, timestamp without time zone, text)\n OWNER TO postgres;\n"
# datetime_sub <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.datetime_sub(timestamp without time zone, interval)\n\n-- DROP FUNCTION mimic4.datetime_sub(timestamp without time zone, interval);\n\nCREATE OR REPLACE FUNCTION mimic4.datetime_sub(\n\tdatetime_val timestamp without time zone,\n\tintvl interval)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN datetime_val - intvl;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.datetime_sub(timestamp without time zone, interval)\n OWNER TO postgres;\n"
# format_date <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.format_date(character varying, timestamp without time zone)\n\n-- DROP FUNCTION mimic4.format_date(character varying, timestamp without time zone);\n\nCREATE OR REPLACE FUNCTION mimic4.format_date(\n\tformat_str character varying,\n\tdatetime_val timestamp without time zone)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_CHAR(\n datetime_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.format_date(character varying, timestamp without time zone)\n OWNER TO postgres;\n"
# format_datetime <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.format_datetime(character varying, timestamp without time zone)\n\n-- DROP FUNCTION mimic4.format_datetime(character varying, timestamp without time zone);\n\nCREATE OR REPLACE FUNCTION mimic4.format_datetime(\n\tformat_str character varying,\n\tdatetime_val timestamp without time zone)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_CHAR(\n datetime_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.format_datetime(character varying, timestamp without time zone)\n OWNER TO postgres;\n"
# generate_array <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.generate_array(integer, integer)\n\n-- DROP FUNCTION mimic4.generate_array(integer, integer);\n\nCREATE OR REPLACE FUNCTION mimic4.generate_array(\n\ti integer,\n\tj integer)\n RETURNS SETOF integer \n LANGUAGE 'sql'\n COST 100\n VOLATILE PARALLEL UNSAFE\n ROWS 1000\n\nAS $BODY$\nSELECT GENERATE_SERIES(i, j)\n$BODY$;\n\nALTER FUNCTION mimic4.generate_array(integer, integer)\n OWNER TO postgres;\n"
# parse_date <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.parse_date(character varying, character varying)\n\n-- DROP FUNCTION mimic4.parse_date(character varying, character varying);\n\nCREATE OR REPLACE FUNCTION mimic4.parse_date(\n\tformat_str character varying,\n\tstring_val character varying)\n RETURNS date\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_DATE(\n string_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.parse_date(character varying, character varying)\n OWNER TO postgres;\n"
# parse_datetime <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.parse_datetime(character varying, character varying)\n\n-- DROP FUNCTION mimic4.parse_datetime(character varying, character varying);\n\nCREATE OR REPLACE FUNCTION mimic4.parse_datetime(\n\tformat_str character varying,\n\tstring_val character varying)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_TIMESTAMP(\n string_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.parse_datetime(character varying, character varying)\n OWNER TO postgres;\n"
# regexp_contains <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.regexp_contains(text, text)\n\n-- DROP FUNCTION mimic4.regexp_contains(text, text);\n\nCREATE OR REPLACE FUNCTION mimic4.regexp_contains(\n\tstr text,\n\tpattern text)\n RETURNS boolean\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN str ~ pattern;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.regexp_contains(text, text)\n OWNER TO postgres;\n"
# regexp_extract <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic4.regexp_extract(text, text)\n\n-- DROP FUNCTION mimic4.regexp_extract(text, text);\n\nCREATE OR REPLACE FUNCTION mimic4.regexp_extract(\n\tstr text,\n\tpattern text)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN substring(str from pattern);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic4.regexp_extract(text, text)\n OWNER TO postgres;\n"
# xname = c("bigquery_format_to_psql", "datetime_add", "datetime_diff",
# "datetime_sub", "datetime1", "datetime2", "format_date", "format_datetime",
# "generate_array", "parse_date", "parse_datetime", "regexp_contains",
# "regexp_extract")
# x=c(bigquery_format_to_psql, datetime_add, datetime_diff,
# datetime_sub, datetime1, datetime2, format_date, format_datetime,
# generate_array, parse_date, parse_datetime,
# regexp_contains, regexp_extract)
# for (i in 1:length(x)) {
# cat(" add",paste0(i,'. ',xname[i],'\n'))
# DBI::dbGetQuery(conn = conn$con,
# statement = x[i])
# }
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Embedding an R snippet on your website
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.