Início / dba / Perguntas / 21473
Accepted
ChrisR
Asked: 2012-07-26 05:19:37 +0800 CST

Acesso dinâmico à coluna de registro na função plpgsql

  • 772

Como posso endereçar uma coluna de um registro em uma função plpgsql dinamicamente?

No trecho a seguir tenho acesso a uma variável entity.colnameque contém a coluna que deve ser verificada na estrutura do controle IF. Estou procurando como posso substituir a foobarparte no trecho abaixo por entity.colname. Isso é possível em plpgsql?

IF NEW.foobar IS NULL THEN
    RAISE EXCEPTION '% cannot be null', foobar;
END IF;

Isso é algo que poderia, mas não funciona.

IF NEW.entity.colname IS NULL THEN
    RAISE EXCEPTION '% cannot be null', entity.colname;
END IF;

O exemplo acima é apenas para ilustrar o que pretendo alcançar, não julgue a funcionalidade. :)

Estou usando o PostgreSQL 9.1.

postgresql plpgsql

2 respostas

  1. Best Answer

    Isso é complicado, porque os identificadores não podem ser variáveis ​​em SQL simples. Você precisa usar SQL dinâmico comEXECUTE - o que ainda é complicado, porque as variáveis ​​não são visíveis dentro EXECUTEde .

    Aqui está uma demonstração de como contornar isso:

    CREATE TYPE mytype AS (id int, txt text);

DO
$body$
DECLARE
    _val    mytype  := (1, NULL)::mytype;
    _name   text    := 'txt';
    _isnull boolean;
BEGIN
    EXECUTE 'SELECT $1.' || quote_ident(_name) || ' IS NULL'
    USING _val
    INTO _isnull;

    IF _isnull THEN
        RAISE NOTICE 'Column "%" cannot be null', _name;
    END IF;
END;
$body$

    Melhorado com a ideia de @Jack no comentário.

    Você não pode usar o plpgsql embutido FOUNDporque não é definido por EXECUTE(exceto por RETURN QUERY EXECUTE- more here ). É por isso que eu usei GET DIAGNOSTICS ...inicialmente. Mas finalmente simplificou com a ideia de @Jack.

    quote_ident()garante que o nome seja sintaticamente válido e protege contra SQLi .

    • 8

  2. supondo que a pergunta seja sobre gatilhos (e não tenho 100% de certeza de que entendi a pergunta), algo nesse sentido pode ser o caminho a percorrer:

    begin;
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
create table t(id serial);
--
create function f() returns trigger language plpgsql set search_path to 'stack' as $$
declare
  s text := 'id';
  b boolean;
begin 
  execute 'select $1.'||s||' is null' using new into b;
  if b then
    raise exception '% cannot be null', s;
  end if;
  return new;
end;$$;
--
create trigger trig before insert on t for each row execute procedure f();
--
insert into t default values;
--INSERT 0 1
insert into t(id) values (null);
--ERROR:  id cannot be null
--
rollback;

    * editado para permitir o caso em que o tipo de coluna é desconhecido (obrigado @Erwin!)

    • 4

relate perguntas