Idempotent DDL snippets

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"
	}
}