[postgresql] PostgreSQL에없는 경우 열을 추가하는 방법은 무엇입니까?

CREATE OR REPLACE function f_add_col(_tbl regclass, _col  text, _type regtype)
  RETURNS bool AS
$func$
BEGIN
   IF EXISTS (SELECT 1 FROM pg_attribute
              WHERE  attrelid = _tbl
              AND    attname = _col
              AND    NOT attisdropped) THEN
      RETURN FALSE;
   ELSE
      EXECUTE format('ALTER TABLE %s ADD COLUMN %I %s', _tbl, _col, _type);
      RETURN TRUE;
   END IF;
END
$func$  LANGUAGE plpgsql;

요구:

SELECT f_add_col('public.kat', 'pfad1', 'int');

TRUE성공시, 그렇지 않으면 FALSE(열이 이미 존재 함) 리턴 합니다.
유효하지 않은 테이블 또는 유형 이름에 대한 예외를 발생시킵니다.

왜 다른 버전입니까?

• 이는 DO명령문 으로 수행 할 수 있지만 DO명령문은 아무것도 리턴 할 수 없습니다. 그리고 반복적으로 사용한다면 함수를 만들 것입니다.

• I는 사용 객체 식별자 유형 regclass 과 regtype대 _tbl하고 _type있는 a) SQL 인젝션 및 B) 모두의 즉시 유효성 검사 (최저가 가능한 방법)을 방지한다. 열 이름 _col은 여전히로 삭제해야 EXECUTE합니다 quote_ident(). 이 관련 답변에 대한 자세한 설명 :

  • PostgreSQL 함수 매개 변수로서의 테이블 이름

• format()Postgres 9.1 이상이 필요합니다. 이전 버전의 경우 수동으로 연결됩니다.

  EXECUTE 'ALTER TABLE ' || _tbl || ' ADD COLUMN ' || quote_ident(_col) || ' ' || _type;

• 테이블 이름을 스키마로 한정 할 수 있지만 반드시 그럴 필요는 없습니다.
  낙타의 경우와 예약어를 보존하기 위해 함수 호출에서 식별자를 큰 따옴표로 묶을 수는 있지만 어쨌든 사용해서는 안됩니다.

• pg_catalog대신에 쿼리 합니다 information_schema. 상해:

  • 주어진 스키마에 테이블이 있는지 확인하는 방법

• 현재 허용되는 답변EXCEPTION 과 같은 절을 포함하는 블록 은 상당히 느립니다. 이것은 일반적으로 더 간단하고 빠릅니다. 설명서 :

팁 : EXCEPTION절이 포함 된 블록은 하나가없는 블록보다 들어오고 나가는 데 훨씬 더 비쌉니다. 따라서 EXCEPTION필요없이 사용 하지 마십시오 .

답변

SELECT EXISTS(SELECT  column_name
                FROM  information_schema.columns
               WHERE  table_schema = 'public'
                 AND  table_name = 'x'
                 AND  column_name = 'y'); 

DO
$$
BEGIN
IF NOT EXISTS (SELECT column_name
                 FROM  information_schema.columns
                WHERE  table_schema = 'public'
                  AND  table_name = 'x'
                  AND  column_name = 'y') THEN
ALTER TABLE x ADD COLUMN y int DEFAULT NULL;
ELSE
RAISE NOTICE 'Already exists';
END IF;
END
$$

ALTER TABLE if exists <tablename> add if not exists <columnname> <columntype>

create or replace function addcol(schemaname varchar, tablename varchar, colname varchar, coltype varchar)
returns varchar
language 'plpgsql'
as
$$
declare
    col_name varchar ;
begin
      execute 'select column_name from information_schema.columns  where  table_schema = ' ||
      quote_literal(schemaname)||' and table_name='|| quote_literal(tablename) || '   and    column_name= '|| quote_literal(colname)
      into   col_name ;

      raise info  ' the val : % ', col_name;
      if(col_name is null ) then
          col_name := colname;
          execute 'alter table ' ||schemaname|| '.'|| tablename || ' add column '|| colname || '  ' || coltype;
      else
           col_name := colname ||' Already exist';
      end if;
return col_name;
end;
$$

CREATE OR REPLACE FUNCTION add_column(schema_name TEXT, table_name TEXT,
column_name TEXT, data_type TEXT)
RETURNS BOOLEAN
AS
$BODY$
DECLARE
  _tmp text;
BEGIN

  EXECUTE format('SELECT COLUMN_NAME FROM information_schema.columns WHERE
    table_schema=%L
    AND table_name=%L
    AND column_name=%L', schema_name, table_name, column_name)
  INTO _tmp;

  IF _tmp IS NOT NULL THEN
    RAISE NOTICE 'Column % already exists in %.%', column_name, schema_name, table_name;
    RETURN FALSE;
  END IF;

  EXECUTE format('ALTER TABLE %I.%I ADD COLUMN %I %s;', schema_name, table_name, column_name, data_type);

  RAISE NOTICE 'Column % added to %.%', column_name, schema_name, table_name;

  RETURN TRUE;
END;
$BODY$
LANGUAGE 'plpgsql';

select add_column('public', 'foo', 'bar', 'varchar(30)');