CODEWITHSUNDEEP
postgresql
Roy
Roy

Reputation: 743

In postgres how can I delete all columns that share the same prefix

I have been using the following code for dropping all tables that share the same prefix (in this case delete all tables that their name starts with 'supenh_'):

DO
$do$
DECLARE
   _tbl text;
BEGIN
FOR _tbl  IN
    SELECT quote_ident(table_schema) || '.'
        || quote_ident(table_name)      -- escape identifier and schema-qualify!
    FROM   information_schema.tables
    WHERE  table_name LIKE 'supenh_' || '%'  -- your table name prefix
    AND    table_schema NOT LIKE 'pg_%'     -- exclude system schemas
LOOP
  --  RAISE NOTICE '%',
   EXECUTE
  'DROP TABLE ' || _tbl;
END LOOP;
END
$do$;

Is there a way to amend this code / or to use a different script in order to delete from one specific table all the columns that start with the same prefix (for example, 'patient1_') ?

Upvotes: 1

Views: 650

Answers (1)

Bohuslav Burghardt
Bohuslav Burghardt

Reputation: 34776

You could write it as PL/pgSQL function:

CREATE OR REPLACE FUNCTION drop_columns_with_prefix(tbl_name TEXT, column_prefix TEXT)  RETURNS VOID AS
$BODY$
DECLARE
    _column TEXT;
BEGIN
    FOR _column  IN
        SELECT quote_ident(column_name)
        FROM   information_schema.columns
        WHERE  table_name = tbl_name
        AND    column_name LIKE column_prefix || '%'
        AND    table_schema NOT LIKE 'pg_%'
    LOOP
      --  RAISE NOTICE '%',
       EXECUTE
      'ALTER TABLE ' || tbl_name || ' DROP COLUMN ' || _column;
    END LOOP;
END
$BODY$
LANGUAGE plpgsql VOLATILE;

Call it using:

SELECT drop_columns_with_prefix('tbl_name', 'prefix_');

Or if you don't want to use it as a function:

DO
$do$
DECLARE
    _column TEXT;
BEGIN
FOR _column  IN
    SELECT quote_ident(column_name)
    FROM   information_schema.columns
    WHERE  table_name = 'tbl_name'
    AND    column_name LIKE 'prefix_%'
    AND    table_schema NOT LIKE 'pg_%'
LOOP
  --  RAISE NOTICE '%',
   EXECUTE
  'ALTER TABLE tbl_name DROP COLUMN ' || _column;
END LOOP;
END
$do$

Upvotes: 4

Related Questions