Skip to content

Instantly share code, notes, and snippets.

@jrf0110

jrf0110/functions.sql

Last active August 8, 2020 15:09
Download ZIP

Revisions

  1. jrf0110 revised this gist Jan 6, 2014. 1 changed file with 16 additions and 0 deletions.
    16 changes: 16 additions & 0 deletions functions.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -19,6 +19,22 @@ begin
    end;
    $$ language plpgsql;

    -- Check constraint existence
    create or replace function constraint_exists( c_type text, tbl_name text, col_name text )
    returns boolean as $$
    begin
    return exists (
    select 1 from
    information_schema.constraint_column_usage usage
    left join information_schema.table_constraints constraints
    on constraints.constraint_name = usage.constraint_name
    where constraints.constraint_type = c_type
    and usage.table_name = tbl_name
    and usage.column_name = col_name
    );
    end;
    $$ language plpgsql;

    -- Add column to table
    create or replace function add_column( tbl_name text, col_name text, col_type text )
    returns void as $$
  2. jrf0110 revised this gist Jan 6, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion functions.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,5 @@
    -- Check for table existence
    create or replace function table_exists ( tbl_name text )
    create or replace function table_exists( tbl_name text )
    returns boolean as $$
    begin
    return exists (
  3. jrf0110 revised this gist Jan 6, 2014. 1 changed file with 12 additions and 1 deletion.
    13 changes: 12 additions & 1 deletion functions.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -8,11 +8,22 @@ begin
    end;
    $$ language plpgsql;

    -- Check for column existence
    create or replace function column_exists( tbl_name text, col_name text )
    returns boolean as $$
    begin
    return exists (
    select column_name from information_schema.columns
    where table_name = tbl_name and column_name = col_name
    );
    end;
    $$ language plpgsql;

    -- Add column to table
    create or replace function add_column( tbl_name text, col_name text, col_type text )
    returns void as $$
    begin
    if not exists ( select 1 from information_schema.columns where table_name = tbl_name and column_name = col_name ) then
    if exists ( select * from column_exists( tbl_name, col_name ) where column_exists = false ) then
    raise notice 'Adding column `%` to table `%`', col_name, tbl_name;
    execute 'alter table "' || tbl_name || '" add column "' || col_name || '" ' || col_type;
    end if;
  4. jrf0110 revised this gist Jan 6, 2014. 1 changed file with 10 additions and 0 deletions.
    10 changes: 10 additions & 0 deletions functions.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,13 @@
    -- Check for table existence
    create or replace function table_exists ( tbl_name text )
    returns boolean as $$
    begin
    return exists (
    select 1 from pg_catalog.pg_tables where tablename = tbl_name
    );
    end;
    $$ language plpgsql;

    -- Add column to table
    create or replace function add_column( tbl_name text, col_name text, col_type text )
    returns void as $$
  5. jrf0110 created this gist Jan 6, 2014.
    21 changes: 21 additions & 0 deletions functions.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,21 @@
    -- Add column to table
    create or replace function add_column( tbl_name text, col_name text, col_type text )
    returns void as $$
    begin
    if not exists ( select 1 from information_schema.columns where table_name = tbl_name and column_name = col_name ) then
    raise notice 'Adding column `%` to table `%`', col_name, tbl_name;
    execute 'alter table "' || tbl_name || '" add column "' || col_name || '" ' || col_type;
    end if;
    end;
    $$ language plpgsql;

    -- Add type
    create or replace function add_type( type_name text, type_def text )
    returns void as $$
    begin
    if not exists ( select 1 from pg_type where typname = type_name ) then
    raise notice 'Adding Type `%`', type_name;
    execute 'create type ' || type_name || ' as ' || type_def;
    end if;
    end;
    $$ language plpgsql;