Início / dba / Perguntas / 279910
Accepted
s.k
Asked: 2020-11-19 09:41:01 +0800 CST

Nomes de coluna que não diferenciam maiúsculas de minúsculas em uma função de gatilho postgresql

  • 772

Eu tenho uma função de gatilho no PostgreSQL 12 que executa algo assim:

CREATE OR REPLACE FUNCTION "my_latlon_function"()
RETURNS trigger AS
$$
BEGIN
    latcolumn:= (
        SELECT column_name
          FROM information_schema.columns
         WHERE table_schema = TG_TABLE_SCHEMA
           AND table_name = TG_TABLE_NAME
           AND column_name ~* '.*lat.*'
    );
    loncolumn := (
        SELECT column_name
          FROM information_schema.columns
         WHERE table_schema = TG_TABLE_SCHEMA
           AND table_name = TG_TABLE_NAME
           AND column_name ~* '.*lon.*'
    );
    EXECUTE 'select $1.' || loncolumn USING NEW INTO lon;
    EXECUTE 'select $1.' || latcolumn USING NEW INTO lat;

    -- do much stuff

    RETURN NEW;

END
$$
LANGUAGE 'plpgsql';

O problema é que as colunas late lontêm letras maiúsculas em seu nome, por exemplo, myLatitudee myLongitude. A função trigger é capaz de recuperar esses nomes, isso não é problema.

O problema está nas duas EXECUTEinstruções em que parece que os nomes das colunas ficam em minúsculas conforme indicado por este erro (na QUERY subjacente quando o gatilho é acionado):

ERROR:  column "mylongitude" not found in data type gpspoints
LINE 1: select $1.myLongitude
               ^
QUERY:  select $1.myLongitude
CONTEXT:  PL/pgSQL function my_latlon_function() line 24 at EXECUTE
********** Error **********

ERROR: column "mylongitude" not found in data type gpspoints
SQL state: 42703
Context: PL/pgSQL function my_latlon_function() line 24 at EXECUTE

Eu sei que o PostgreSQL precisa colocar os nomes das colunas que têm letras maiúsculas em seu nome entre aspas duplas. Portanto, tentei definir aspas duplas nas duas instruções EXECUTE como esta:

    EXECUTE 'select $1.' || "loncolumn" USING NEW INTO lon;
    EXECUTE 'select $1.' || "latcolumn" USING NEW INTO lat;

Mas o erro permanece exatamente o mesmo.

Se possível, como posso lidar com nomes de colunas CamelCase em uma função de gatilho do PostgreSQL?

Se não não, por quê?

postgresql trigger

2 respostas

  1. Best Answer

    Use formatcom o %Iespaço reservado para identificadores:

    EXECUTE format('select $1.%I', latcolumn) USING NEW INTO lat;
    • 2

  2. Identificadores de aspas duplas automaticamente quando necessário com format()(como Laurenz demonstra) ou quote_ident(). Ver:

    Além disso, sua função de gatilho pode ser mais eficiente assim:

    CREATE OR REPLACE FUNCTION my_latlon_function()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   _latcol text;
   _loncol text;
   _lat numeric;
   _lon numeric;
BEGIN
   SELECT quote_ident(a.attname) INTO _latcol  -- properly quoted!
   FROM   pg_attribute a
   WHERE  a.attrelid = TG_RELID
   AND    a.attname ~* 'lat'
   AND    a.attnum > 0
   AND    NOT a.attisdropped;

   SELECT quote_ident(a.attname) INTO _loncol
   FROM   pg_attribute a
   WHERE  a.attrelid = TG_RELID
   AND    a.attname ~* 'lon'
   AND    a.attnum > 0
   AND    NOT a.attisdropped;
   
   EXECUTE concat('SELECT $1.', _latcol, ', $1.', _loncol)
   USING  NEW
   INTO   _lat, _lon;

   RAISE NOTICE '% / %', _lat, _lon;  -- debug output

   -- do much stuff

   RETURN NEW;
END
$func$;

    db<>fique aqui

    (Isso pressupõe que os nomes das colunas sempre correspondam sem ambiguidade!?)

    Demonstrando quote_ident(). Com os nomes das colunas já citados corretamente, podemos simplesmente concat()mais tarde.

    A tabela de catálogo pg_catalog.pg_attributeé notavelmente mais rápida que information_schema.columns. Compare a saída desses dois comandos para entender o porquê:

    EXPLAIN SELECT * FROM information_schema.columns;

    EXPLAIN SELECT * FROM pg_catalog.pg_attribute;

    Ver:

    Nesse caso específico, também podemos usar convenientemente TG_RELIDem vez de TG_TABLE_SCHEMAe TG_TABLE_NAME.

    Além disso, atribuições individuais são comparativamente caras em PL/pgSQL. Tente manter seu número baixo.

    Embora a diferença de desempenho ainda seja pequena, essa função de gatilho é chamada uma vez por linha inserida. Adiciona-se para grandes inserções ...

    Além disso, col ~* 'lon'é exatamente equivalente a col ~* '.*lon.*'.

    • 2

relate perguntas