Skip to content

Instantly share code, notes, and snippets.

@thelinuxlich

thelinuxlich/sql.json

Last active July 28, 2023 21:17
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save thelinuxlich/f43466c9ff50e3753f8a305f13272b93 to your computer and use it in GitHub Desktop.
Save thelinuxlich/f43466c9ff50e3753f8a305f13272b93 to your computer and use it in GitHub Desktop.
Download ZIP
Idempotent DDL snippets
Raw
sql.json
{
"Create Schema": {
"prefix": "psql - Create new schema",
"body": [
"DO $$",
"DECLARE",
"\tv_rec varchar;",
"\tv_schema varchar[] := array[ '$1', '$2' .... ];",
"BEGIN",
"\tFOREACH v_rec IN ARRAY v_schema",
"\t\tLOOP",
"\t\t\tIF NOT EXISTS ( SELECT 1 FROM information_schema.schemata WHERE schema_name = v_rec ) THEN",
"\t\t\t\tRAISE INFO 'Creating schema %', v_rec;",
"\t\t\t\t-- E.g. CREATE SCHEMA foo;",
"\t\t\t\tEXECUTE 'CREATE SCHEMA ' || v_rec || ';';",
"\t\t\tELSE",
"\t\t\t\tRAISE INFO 'Schema % already exists', v_rec;",
"\t\t\tEND IF;",
"\t\tEND LOOP;",
"END",
"$$"
],
"description": "Create new schema if it does not exist"
},
"Drop Schema": {
"prefix": "psql - Drop existing schema",
"body": [
"DO $$",
"DECLARE",
"\tv_rec varchar;",
"\tv_schema varchar[] := array[ '$1', '$2' .... ];",
"\tBEGIN",
"\tFOREACH v_rec IN ARRAY v_schema",
"\t\tLOOP",
"\t\t\tIF EXISTS ( SELECT 1 FROM information_schema.schemata WHERE schema_name = v_rec ) THEN",
"\t\t\t\tRAISE INFO 'Dropping schema %', v_rec;",
"\t\t\t\t-- E.g. DROP SCHEMA foo;",
"\t\t\t\tEXECUTE 'DROP SCHEMA ' || v_rec || ';';",
"\t\t\tELSE",
"\t\t\t\tRAISE INFO 'Schema % doesn''t exists', v_rec;",
"\t\t\tEND IF;",
"\t\tEND LOOP;",
"END",
"$$"
],
"description": "Drop a schema if it exists"
},
"Create Table": {
"prefix": "psql - Create new table",
"body": [
"DO $$",
"DECLARE",
"\tv_database varchar := '$1';",
"\tv_schema varchar := 'public';",
"\tv_table varchar := '$3';",
"BEGIN",
"\tIF NOT EXISTS ( SELECT 1 FROM information_schema.tables WHERE table_catalog = v_database AND table_schema = v_schema AND table_name = v_table ) THEN",
"\t\tRAISE INFO 'Creating table %.%', v_schema, v_table;",
"\t\t/* E.g. CREATE TABLE foo.bar (",
"\t\t\tid serial primary key,",
"\t\t\tname varchar(25)",
"\t\t);*/",
"\t\tEXECUTE 'CREATE TABLE ' || v_schema || '.' || v_table || ' (",
"\t\t\t${4:Column Definitions}",
"\t\t);';",
"\tELSE",
"\t\tRAISE INFO 'TABLE %.% already exists', v_schema, v_table;",
"\tEND IF;",
"END",
"$$"
],
"description": "Create new table if it does not exist"
},
"Drop Table": {
"prefix": "psql - Drop existing table",
"body": [
"DO $$",
"DECLARE",
"\tv_database varchar := '$1';",
"\tv_schema varchar := 'public';",
"\tv_table varchar := '$3';",
"BEGIN",
"\tIF EXISTS ( SELECT 1 FROM information_schema.tables WHERE table_catalog = v_database AND table_schema = v_schema AND table_name = v_table ) THEN",
"\t\tRAISE INFO 'Dropping %.%', v_schema, v_table;",
"\t\t/* E.g. DROP TABLE foo.bar; */",
"\t\tEXECUTE 'DROP TABLE ' || v_schema || '.' || v_table || ';'",
"\tELSE",
"\t\tRAISE INFO 'TABLE %.% does not exist', v_schema, v_table;",
"\tEND IF;",
"END",
"$$"
],
"description": "Drop table if it exists"
},
"Add Type": {
"prefix": "psql - Add new type",
"body": [
"DO $$",
"DECLARE",
"\tv_schema varchar := 'public';",
"\tv_type varchar := 'enum_type';",
"BEGIN",
"\tIF NOT EXISTS ( SELECT 1 FROM pg_type WHERE typname = v_type ) THEN",
"\t\tRAISE INFO 'Creating type %', v_type;",
"\t\t-- E.g. CREATE TYPE foo.bar AS ENUM ('a', 'b', 'c');",
"\t\tEXECUTE 'CREATE TYPE ' || v_schema || '.' || v_type || ' AS ENUM (''${4:Value 1}'', ''${5:Value 2}'', ''${6:Value 3}'');';",
"\tELSE",
"\t\tRAISE INFO 'TYPE % already exists', v_type;",
"\tEND IF;",
"END",
"$$"
],
},
"Drop Type": {
"prefix": "psql - Drop existing type",
"body": [
"DO $$",
"DECLARE",
"\tv_schema varchar := 'public';",
"\tv_type varchar := 'enum_type';",
"BEGIN",
"\tIF EXISTS ( SELECT 1 FROM pg_type WHERE typname = v_type ) THEN",
"\t\tRAISE INFO 'Dropping type %', v_type;",
"\t\t-- E.g. DROP TYPE foo.bar;",
"\t\tEXECUTE 'DROP TYPE ' || v_schema || '.' || v_type || ';';",
"\tELSE",
"\t\tRAISE INFO 'TYPE % does not exist', v_type;",
"\tEND IF;",
"END",
"$$"
],
"description": "Drop type if it exists"
},
"Add Enum Type to Column": {
"prefix": "psql - Add enum type to column",
"body": [
"DO $$",
"DECLARE",
"\tv_database varchar := '$1';",
"\tv_schema varchar := 'public';",
"\tv_table varchar := '$3';",
"\tv_column varchar := '$4';",
"\tv_type varchar := '$5';",
"BEGIN",
"\tIF EXISTS ( SELECT 1 FROM pg_type WHERE typname = v_type ) AND EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_catalog = v_database AND table_schema = v_schema AND table_name = v_table AND column_name = v_column ) THEN",
"\t\tRAISE INFO 'Adding type % to column %', v_type, v_column;",
"\t\t-- E.g. ALTER TABLE foo.bar ALTER COLUMN baz TYPE foo.bar;",
"\t\tEXECUTE 'ALTER TABLE ' || v_schema || '.' || v_table || ' ALTER COLUMN ' || v_column || ' TYPE ' || v_schema || '.' || v_type || 'USING' || v_type || '::' || v_column || ';';",
"\tELSE",
"\t\tRAISE INFO 'Column % or type does not exist', v_column;",
"\tEND IF;",
"END",
"$$"
],
"description": "Change column type to enum if it exists"
},
"Add Column": {
"prefix": "psql - Add new column",
"body": [
"DO $$",
"DECLARE",
"\tv_database varchar := '$1';",
"\tv_schema varchar := 'public';",
"\tv_table varchar := '$3';",
"\tv_column varchar := '$4';",
"BEGIN",
"\tIF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_catalog = v_database AND table_schema = v_schema AND table_name = v_table AND column_name = v_column) THEN",
"\t\tRAISE INFO 'Adding column % to table %.%', v_column, v_schema, v_table;",
"\t\t-- E.g. ALTER TABLE foo.bar ADD COLUMN baz varchar(25)",
"\t\tEXECUTE 'ALTER TABLE ' || v_schema || '.' || v_table || ' ADD COLUMN ' || v_column || ' ${5:Column Type & defaults etc};';",
"\tELSE",
"\t\tRAISE INFO 'Column % already exists on table %.%', v_column, v_schema, v_table;",
"\tEND IF;",
"END",
"$$"
],
"description": "Add new column if it does not exist"
},
"Alter Column": {
"prefix": "psql - Alter a column",
"body": [
"DO $$",
"DECLARE",
"\tv_database varchar := '$1';",
"\tv_schema varchar := 'public';",
"\tv_table varchar := '$3';",
"\tv_column varchar := '$4';",
"BEGIN",
"\tIF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_catalog = v_database AND table_schema = v_schema AND table_name = v_table AND column_name = v_column) THEN",
"\t\tRAISE INFO 'Altering column % to table %.%', v_column, v_schema, v_table;",
"\t\t-- E.g. ALTER TABLE foo.bar ALTER COLUMN baz TYPE varchar(25);",
"\t\tEXECUTE 'ALTER TABLE ' || v_schema || '.' || v_table || ' ALTER COLUMN ' || v_column || ' TYPE ${5:Column Type & defaults etc};';",
"\tELSE",
"\t\tRAISE INFO 'Column % does not exists on table %.%', v_column, v_schema, v_table;",
"\tEND IF;",
"END",
"$$"
],
"description": "Alter column if it exists"
},
"Drop Column": {
"prefix": "psql - Drop a column",
"body": [
"DO $$",
"DECLARE",
"\tv_database varchar := '$1';",
"\tv_schema varchar := 'public';",
"\tv_table varchar := '$3';",
"\tv_column varchar := '$4';",
"BEGIN",
"\tIF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_catalog = v_database AND table_schema = v_schema AND table_name = v_table AND column_name = v_column) THEN",
"\t\tRAISE INFO 'Dropping column % to table %.%', v_column, v_schema, v_table;",
"\t\t-- E.g. ALTER TABLE foo.bar DROP COLUMN baz;",
"\t\tEXECUTE 'ALTER TABLE ' || v_schema || '.' || v_table || ' DROP COLUMN ' || v_column || ';';",
"\tELSE",
"\t\tRAISE INFO 'Column % does not exists on table %.%', v_column, v_schema, v_table;",
"\tEND IF;",
"END",
"$$"
],
"description": "Drop column if it exists"
},
"Add Constraint": {
"prefix": "psql - Add new constraint",
"body": [
"DO $$",
"DECLARE",
"\tv_database varchar := '$1';",
"\tv_schema varchar := 'public';",
"\tv_table varchar := '$3';",
"\tv_column varchar := '$4';",
"\tv_constraint varchar := '$5';",
"BEGIN",
"\tIF NOT EXISTS (SELECT 1 FROM information_schema.constraint_column_usage WHERE table_catalog = v_database AND table_schema = v_schema AND table_name = v_table AND column_name = v_column AND constraint_name = v_constraint) THEN",
"\t\tRAISE INFO 'Adding constraint % to table %.%', v_constraint, v_schema, v_table;",
"\t\t-- E.g. ALTER TABLE foo.bar ADD CONSTRAINT enforce_srid_geometry CHECK (ST_SRID(geometry) = 4326);",
"\t\tEXECUTE 'ALTER TABLE ' || v_schema || '.' || v_table || ' ADD CONSTRAINT ' || v_constraint || ' ${6:Contraint definition};';",
"\tELSE",
"\t\tRAISE INFO 'Constraint % already exists on table %.%', v_constraint, v_schema, v_table;",
"\tEND IF;",
"END",
"$$"
],
"description": "Add new constraint if it does not exist"
},
"Drop Constraint": {
"prefix": "psql - Drop existing constraint",
"body": [
"DO $$",
"DECLARE",
"\tv_database varchar := '$1';",
"\tv_schema varchar := 'public';",
"\tv_table varchar := '$3';",
"\tv_column varchar := '$4';",
"\tv_constraint varchar := '$5';",
"BEGIN",
"\tIF EXISTS (SELECT 1 FROM information_schema.constraint_column_usage WHERE table_catalog = v_database AND table_schema = v_schema AND table_name = v_table AND column_name = v_column AND constraint_name = v_constraint) THEN",
"\t\tRAISE INFO 'Dropping constraint % on table %.%', v_constraint, v_schema, v_table;",
"\t\t-- E.g. ALTER TABLE foo.bar DROP CONSTRAINT enforce_srid_geometry;",
"\t\tEXECUTE 'ALTER TABLE ' || v_schema || '.' || v_table || ' DROP CONSTRAINT ' || v_constraint || ';';",
"\tELSE",
"\t\tRAISE INFO 'Constraint % does not exists on table %.%', v_constraint, v_schema, v_table;",
"\tEND IF;",
"END",
"$$"
],
"description": "Drop existing constraint if it exists"
},
"Add Index": {
"prefix": "psql - Add index",
"body": [
"DO $$",
"DECLARE",
"\tv_database varchar := '$1';",
"\tv_schema varchar := 'public';",
"\tv_table varchar := '$3';",
"\tv_column varchar := '$4';",
"\tv_type varchar := 'btree';",
"\tv_index_name varchar := 'idx_$4_$3';",
"BEGIN",
"\tIF NOT EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = v_index_name AND n.nspname = v_schema) THEN",
"\t\tRAISE INFO 'Adding index %', v_index_name;",
"\t\t-- E.g. CREATE INDEX idx_name_btree ON foo.bar USING btree(name);",
"\t\tEXECUTE 'CREATE INDEX ' || v_index_name || ' ON ' || v_schema || '.' || v_table || ' USING ' || v_type || '(' || v_column || ');'",
"\tELSE",
"\t\tRAISE INFO 'INDEX % already exists on table %.%', v_index_name, v_schema, v_table;",
"\tEND IF;",
"END",
"$$"
],
"description": "Add a new index if it does not exist"
},
"Drop Index": {
"prefix": "psql - Drop index",
"body": [
"DO $$",
"DECLARE",
"\tv_database varchar := '$1';",
"\tv_schema varchar := 'public';",
"\tv_table varchar := '$3';",
"\tv_column varchar := '$4';",
"\tv_index_name varchar := 'idx_$4_$3';",
"BEGIN",
"\tIF EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = v_index_name AND n.nspname = v_schema) THEN",
"\t\tRAISE INFO 'Dropping index %', v_index_name;",
"\t\t-- E.g. DROP INDEX idx_name_btree;",
"\t\tEXECUTE 'DROP INDEX ' || v_schema || '.' || v_index_name || ';';",
"\tELSE",
"\t\tRAISE INFO 'INDEX % doesn''t exists', v_index_name;",
"\tEND IF;",
"END",
"$$"
],
"description": "Drop an index if it exists"
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment