Roberto Iglesias's questions

Estou encontrando um problema com uma função PL/pgSQL em que ela retorna diferentes tipos de dados com base em uma condição e resulta em um erro de incompatibilidade de tipo. Aqui está uma versão simplificada da função:

CREATE OR REPLACE FUNCTION public.test(lever int)
    RETURNS VARCHAR
    LANGUAGE plpgsql
AS $function$
DECLARE
    _new_record RECORD;
BEGIN
    -- Evaluating the random condition and returning different strings
    IF (lever = 0) THEN
        SELECT * FROM
        (VALUES(uuid_generate_anura()))x(id)
        INTO _new_record;
    ELSE
        SELECT * FROM
        (VALUES(10))x(id)
        INTO _new_record;
    END IF;
    RETURN pg_typeof(_new_record.id)::varchar;
END;
$function$;

Observe que esta é uma versão muito simplificada da minha função completa. Estou interessado em entender por que e como posso contornar isso.

Ao chamar esta função com lever = 0, ela retorna corretamente o texto uuid. No entanto, ao chamá-lo lever = 1para forçar a ELSEexecução da instrução, ocorre um erro:

postgres=# select test(0);
 test 
------
 uuid
(1 row)

postgres=# select test(1);
ERROR:  type of parameter 4 (integer) does not match that when preparing the plan (uuid)
CONTEXT:  PL/pgSQL function test(integer) line 15 at RETURN

Não importa o tipo de dados, o ELSEbloco sempre falhará.

Estou enfrentando um problema ao implementar uma regra ou gatilho no PostgreSQL para lidar com inserções em uma tabela onde um dos tipos de coluna ( a) foi alterado e consultas antigas. Tentei algumas abordagens, mas não consegui alcançar o comportamento desejado.

Aqui está uma versão simplificada do meu cenário:

Eu tenho uma tabela t1com colunas ae b:

CREATE TABLE t1 (
    a int,
    b int
);

Quero permitir consultas que venham com outro tipo para column a, como uma string, e substituí-lo por outro valor, digamos 0. No entanto, se um valor inteiro for inserido, ele deverá ser preservado sem modificação.

Tentei usar uma regra e uma função de gatilho, mas estou encontrando problemas com cada abordagem:

1. Regra : tentei criar uma regra para interceptar inserções e modificar o valor da coluna ase não for um número inteiro. No entanto, enfrentei problemas com recursão infinita e a regra não sendo acionada para entradas inválidas:

CREATE RULE test_rule AS
    ON INSERT TO t1
    WHERE pg_typeof(NEW.a) <> 'integer'::regtype
    DO INSTEAD
        INSERT INTO t1 (a, b)
        VALUES (0, NEW.b)

postgres=# insert into t1 (select 'x',1);
ERROR:  invalid input syntax for type integer: "x"
LINE 1: insert into t1 (select 'x',1);
                               ^
postgres=# insert into t1 (select 1,1);
ERROR:  infinite recursion detected in rules for relation "t1"

2. Função de gatilho : tentei usar uma função de gatilho para verificar o tipo de dados do valor inserido e modificá-lo de acordo. Embora essa abordagem funcionasse para entradas válidas, ela não tratava entradas inválidas conforme o esperado.

CREATE OR REPLACE FUNCTION insert_default_a_if_non_integer()
RETURNS TRIGGER AS
$$
BEGIN
    IF pg_typeof(NEW.a) <> 'integer'::regtype THEN
        NEW.a := 0;  -- or any default value you prefer
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER t1_insert_trigger
BEFORE INSERT ON t1
FOR EACH ROW
EXECUTE FUNCTION insert_default_a_if_non_integer();

postgres=# insert into t1 (select 1,1);
INSERT 0 1
postgres=# insert into t1 (select 'x',1);
ERROR:  invalid input syntax for type integer: "x"
LINE 1: insert into t1 (select 'x',1);
                               ^

Alguém poderia fornecer orientação sobre a melhor abordagem para atender às minhas necessidades? Quaisquer insights ou soluções alternativas seriam muito apreciadas.

Obrigado!

Tenho um cenário de teste de dois bancos de dados postgresql conectados via replicação lógica. O BD "A" está publicando inserções e atualizações para a tabela "a1" e o BD "B" está cadastrado e recebendo tudo sem problemas.

Adicionei um gatilho antes da atualização para a tabela "a1" no banco de dados "B". Ele está funcionando para instruções de atualização executadas localmente em "B", mas não é acionado quando a atualização ocorre por meio de replicação lógica. Aqui está a função e o gatilho que estou usando para testá-lo.

CREATE OR REPLACE FUNCTION update_tg_func()
RETURNS TRIGGER AS $$
BEGIN
    -- Extract the id value from the update query
    -- Assuming that the 'id' column is being updated
    RAISE WARNING 'Got OP %, id %', TG_OP, NEW.id;
    -- For other operations, just return the NEW row
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER update_tg
BEFORE UPDATE ON a1
FOR EACH STATEMENT
EXECUTE FUNCTION update_tg_func();

ALTER TABLE a1 ENABLE ALWAYS TRIGGER update_tg ;

A versão do Postgres é 12.1, não testada em versões mais recentes.

EDITAR

Graças ao @Melkij notei que meu gatilho não está disparando porque minha mesa está vazia. E minha tabela está vazia porque tenho outro gatilho BEFORE INSERTque "redireciona" as linhas inseridas para outras tabelas, assim como faria o particionamento, mas sem usar particionamento real.

Achei que o uso FOR EACH STATEMENTacionaria o gatilho mesmo que nenhuma linha fosse afetada. A documentação diz:

um gatilho marcado é FOR EACH STATEMENTexecutado apenas uma vez para qualquer operação, independentemente de quantas linhas ele modifica https://www.postgresql.org/docs/12/sql-createtrigger.html

Removi a trigger que "redireciona" as linhas inseridas para que minha tabela no DB B tenha algumas linhas e agora a trigger dispara.

Preciso que a trigger seja acionada mesmo que a tabela esteja vazia. Alguma dica?