Início / dba / Perguntas / 35683
Accepted
GuidoS
Asked: 2013-03-01 09:09:55 +0800 CST

Manipulação de strings no postgres 8.4

  • 772

Isso começou como uma pergunta sobre índices, mas quando recebi minha resposta , percebi que teria que tentar converter a resposta para a sintaxe 8.4 (não acredito que esse formato não exista).

Achei que estava lidando com as variáveis ​​corretamente, mas por algum motivo não consigo fazer a digitação funcionar corretamente:

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 'SELECT COLUMN_NAME FROM information_schema.columns WHERE 
     table_schema='''+|| schema_name+'''
     AND table_name='''+||table_name+'''
     AND column_name='''+||column_name+''''   INTO _tmp;

   IF _tmp IS NOT NULL THEN
     RAISE NOTICE USING MESSAGE = 'Column '+||column_name+' already exists in '+||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 USING MESSAGE = 'Column '+||column_name+' added to '+||schema_name+'.'+||table_name;

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

Aqui está a mensagem de erro que recebo:

ERRO: operador não existe: texto + desconhecido

Alguém sabe como posso fazer isso funcionar no 8.4?

postgresql plpgsql

2 respostas

  1. Best Answer

    Sua função teve vários problemas. Experimente esta versão revisada:

    CREATE OR REPLACE FUNCTION add_column(
  full_name   regclass
 ,column_name text
 ,data_type   regtype)
  RETURNS BOOLEAN AS
$func$
BEGIN

IF EXISTS (
      SELECT 1
      FROM   pg_attribute
      WHERE  attrelid = full_name
      AND    attname = column_name
      AND    attisdropped = FALSE) THEN

   RAISE NOTICE 'Column "%" % already exists in %.'
                ,column_name, data_type, full_name;
   RETURN FALSE;
END IF;

-- For Postgres 9.1+:
EXECUTE format('ALTER TABLE %s ADD COLUMN %I %s;'
                ,full_name, column_name, data_type); 

-- For Postgres 8.4-:
-- EXECUTE 'ALTER TABLE '|| full_name
--      || ' ADD COLUMN ' || quote_ident(column_name) || ' ' || data_type;

RAISE NOTICE 'Column "%" % added to %.'
             ,column_name, data_type, full_name;
RETURN TRUE;

END
$func$ LANGUAGE plpgsql;

    Testado com PostgreSQL 9.1.7.

    • Você não precisa de SQL dinâmico para verificar a(s) tabela(s) do catálogo quanto à existência da coluna. EXISTSSimplifique isso para uma expressão simples .

    • É regularmente mais rápido em duas ordens de magnitude usar a tabela pg_catalog.pg_attributeem vez da exibição inchada information_schema.columns.

      information_schemaé bom apenas para conformidade com o padrão SQL e portabilidade do código. Como você está escrevendo código 100% específico do Postgres em uma função plpgsql, nenhum dos dois é relevante aqui. E o layout básico de pg_attribute também não será alterado nas principais versões do Postgres.

    • format()foi introduzido com o Postgres 9.1 . Use esta função conveniente no Postgres moderno. Para a versão 8.4 e anteriores, use a alternativa comentada no código.

    • Não cite o nome do idioma plpgsqlno final! É um identificador, não um valor. Aspas simples Farão o comando falhar ao usar letras maiúsculas. Geralmente, é essencial saber a diferença entre identificadores e valores.

    • Substituí os dois parâmetros schema_name text, table_name textpor fullname regclass. Isso tem várias vantagens e nenhuma desvantagem AFAICS.

      1. Código mais curto e chamada mais simples.
      2. É simples fornecer o nome do esquema quando necessário e omiti-lo quando não.
      3. Cast to regclassverifica a existência da tabela imediatamente e gera uma exceção se não. Também impede SQLi de forma eficaz.
      4. Se você não fornecer um nome qualificado pelo esquema, o search_pathserá usado automaticamente. Se o nome da tabela for inequívoco, dado o atual search_path, o nome do esquema também não será impresso quando convertido em texto.
      5. Quando (automaticamente) convertido para text, o esquema e/ou o nome da tabela são automaticamente colocados entre aspas quando necessário. Elimina a necessidade de quote_ident(), escapando como identificador format()ou adicionando aspas em mensagens.

    • Da mesma forma, eu uso regtypepara o arquivo data_type. Vantagens semelhantes. Leia mais sobre os tipos de identificador de objeto no manual.

    • RAISESintaxe mais simples .

    • O operador de concatenação no Postgres (e no SQL padrão) é ||. +é uma extensão do padrão no SQL Server que não funciona com a maioria dos outros RDBMS.

    • 3

  2. Isso funciona (não testado extensivamente):

    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 'SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema='''
           || schema_name || ''' AND table_name=''' 
           || table_name ||''' AND column_name=''' 
           ||column_name ||'''' INTO _tmp;

   IF _tmp IS NOT NULL THEN 
            RAISE NOTICE USING MESSAGE = 'Column ' || column_name 
                         || ' already exists in ' || schema_name 
                         || '.' ||table_name; 
            RETURN FALSE; 
   END IF;

   EXECUTE 'ALTER TABLE ' || schema_name || '.' || table_name 
         || ' ADD COLUMN ' || column_name || ' ' || data_type || ';';

   RAISE NOTICE USING MESSAGE = 'Column ' || column_name ||' added to ' 
           || schema_name ||'.'|| table_name;

   RETURN TRUE; 
END; 
$BODY$ LANGUAGE plpgsql;

    Eu simplesmente removi os sinais + e substituí por ||. No EXECUTE removi o formato e simplesmente concatenei as strings.

    • 0

relate perguntas