[pgbr-geral] dificuldade com trigger postgresql em json

[Amir]

Necessidade: Toda a vez que uma cities for colocada ou retirada da table
reach.address_012, esta deve ser somada ou decrescida do campo
units-->’number_of_units’ tabela reach.address_010 de 1(um); Lembramos que
cities-->’unit_of_the_federation’ da reach.address_012 é ligado a
units-->"acronym" na reach.address_010.

Tenho estas tabelas:

CREATE TABLE reach.address_010 (
units reach.dmn_jsonb
) WITH (oids = false);

COMMENT ON TABLE reach.address_010 IS ' The unit of the federation';

CREATE INDEX units_id_address12_index ON reach.address_010
USING gin (((units)::jsonb->' unit_id'::text));
Com os seguintes campos:

'{" unit_id": 9, "state”:{"name": " Michigan ", "acronym": "MI"},
"number_of_units", 3283}';

CREATE TABLE reach.address_012 (
cities reach.dmn_jsonb
) WITH (oids = false);

COMMENT ON TABLE reach.address_012 IS ' The cities name';

CREATE INDEX cities_city_id_address12_index ON reach.address_012
USING gin (((cities)::jsonb->'city_id'::text));
Com os seguintes campos:

'{"city_id": 1, "city_base": {"city_name": "Lansing", "city_type": "C",
"postal_code": "48823517", "city_situation": 0, "unit_of_the_federation":
"MI"}, "country_code": 1}';

Dificuldade: Não consigo definir a trigger, pois não entendo como devo
utilizar os campos “old” e “new” em uma base jsonb, se fosse uma tabela
relacional, seria assim:

Na tabela hipotética relacional reach.address_010

CREATE FUNCTION reach.func_before_update_of_number_of_units_address10()
RETURNS trigger AS
$body$
begin
new.number_of_units := old.number_of_units + new.number_of_units;
return new;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE CALLED ON NULL
INPUT SECURITY
INVOKER COST 100;


CREATE TRIGGER trigger_before_update_of_number_of_units_address10
BEFORE UPDATE OF number_of_units ON reach.address_010 FOR EACH ROW
EXECUTE PROCEDURE func_before_update_of_number_of_units_address10();

Na tabela hipotética relacional reach.address_012

CREATE OR REPLACE FUNCTION reach.func_after_insert_delete_address12()
RETURNS trigger AS
$body$
declare
characronym reach.dmn_vc002 = '';
smallint_number reach.dmn_smallint = 0;
begin
if (TG_OP = 'DELETE') then
characronym := old.unit_of_the_federation;
smallint_number := -1;
else
characronym := new.unit_of_the_federation;
smallint_number := 1;
end if;

update reach.address_010
set number_of_units = smallint_number
where acronym = characronym;

return new;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE CALLED ON NULL
INPUT SECURITY
INVOKER COST 100;

CREATE TRIGGER trigger_after_insert_delete_address12
AFTER INSERT OR DELETE ON reach.address_012 FOR EACH ROW
EXECUTE PROCEDURE reach.func_after_insert_delete_address12();

Alguém do grupo pode ajudar-me!

Amir Hereibi