Função Postgresql identificar dados cadastrados

Olá sou nova na area de banco de dados, estou fazendo uma função que identifique dados já cadastrados que são o nome e numero de ramal da pessoa. A função funcionou para o ramal mas preciso incluir o nome, como eu poderia fazer? PS: estou trabalhando com jsonb


CREATE OR REPLACE FUNCTION public.inserir5(
p_idramal integer,
p_infos jsonb)
RETURNS text
LANGUAGE 'plpgsql'
COST 100
AS $BODY$
declare
v_ramal text;
begin
if ( SELECT EXISTS (SELECT infos from ramais where infos->>'nr_ramal' = p_infos->>'nr_ramal') ) then
return ' ja cadastrado no Banco';
else
insert into ramais (id_ramal,infos) values (p_idramal, p_infos);
return 'informacoes cadastradas com sucesso !!';
end if;
end;
$BODY$;

___________________________________________________________________________________
[
{
"nm_setor": "protocolo",
"nr_ramal": "2256",
"nm_servidor": "paula"
},
{
"nm_setor": "secretaria",
"nr_ramal": "2269",
"nm_servidor": "livia"
},
{
"nm_setor": "financeiro",
"nr_ramal": "2217",
"nm_servidor": "anna"
},
{
"nm_setor": "logistica",
"nr_ramal": "2006",
"nm_servidor": "jose"
}]

{
"error": {
"message": "We could not parse the JSON body of your request. (HINT: This likely means you aren't using your HTTP library correctly. The OpenAI API expects a JSON payload, but what was sent was not valid JSON. If you have trouble figuring out how to fix this, please send an email to support@openai.com and include any relevant code you'd like help with.)",
"type": "invalid_request_error",
"param": null,
"code": null
}
}

O nome, o ramal e o protocolo estão na mesma tabela chamada ramais ou estão em tabelas diferentes?


________________________________________________
Always listen the Buck!

Estão todos na mesma tabela, para ajudar estou anexando algumas imagens. Já tentei fazer algumas funções que inclui está view mas ela pega o id novo e cadastra novamente o mesmo ramal o que não deveria acontecer pois não devem ter ramais iguais.

Bom, de início aconselho a colocar o registro do número do ramal como Unique Constraint:
https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS

Provavelmente você está usando o Pgadmin, então pode fazer isso no Pgadmin, senão faça na linha de comando mesmo. Com a chave unique o próprio banco impede dois registros iguais.

Sobre as funções darei uma olhada com mais calma, mas adianto que se você já tem uma função que funciona para o ramal então provavelmente é só incluir o nome na mesma função, pois estão na mesma tabela.
https://www.postgresql.org/docs/15/functions-json.html

E pelas imagens dá para perceber que você quer é fazer uma validação dos dados antes de cadastrá-los.
Aconselho a fazer isso na aplicação e no banco setar a chave unique constraint, depois é só fazer os selects, updates, inserts, etc.
Mas se você realmente precisa fazer isso no banco, caso sejam milhões de dados que serão cadastrados, então tem que raciocinar com calma.


________________________________________________
Always listen the Buck!

Olá, muito obrigado pela dica ainda não tinha pesquisado sobre. Tentei executar criando somente no ramal mas não obtive sucesso, vou continuar estudando aqui, ele tem que barrar o mesmo ramal, e não consegui novamente. Uma pessoa pode ter vários números mas a condição é que não exista nenhum igual.

create table ramais (
id_ram integer not null,
infos jsonb not null
);

select *from ramais;
create unique index on ramais (id_ram, (infos->>'nr_ramal'));

insert into ramais (id_ram,infos) values
(1,'[{"nm_setor":"protocolo","nm_servidor":"Pedro", "nr_ramal": "2256"},
{"nm_setor":"secretaria","nm_servidor":"Vitor", "nr_ramal": "2269"},
{"nm_setor":"financeiro","nm_servidor":"Ana", "nr_ramal": "2216"}]');

insert into ramais (id_ram,infos) values
(2,'[{"nm_setor":"protocolo","nm_servidor":"Paulo", "nr_ramal": "2756"},
{"nm_setor":"secretaria","nm_servidor":"Vitoria", "nr_ramal": "2279"},
{"nm_setor":"financeiro","nm_servidor":"Anna", "nr_ramal": "2716"}]');

insert into ramais (id_ram,infos) values
(3,'[{"nm_setor":"protocolo","nm_servidor":"Patrick", "nr_ramal": "2246"},
{"nm_setor":"secretaria","nm_servidor":"Vanessa", "nr_ramal": "2249"},
{"nm_setor":"financeiro","nm_servidor":"Igor", "nr_ramal": "2416"}]');

CREATE VIEW select_ramais AS
select infos->'nm_servidor' as servidor, infos->'nr_ramal' AS ramal
from ramais where infos->>'nm_servidor' = 'Anna';

--nao ta puxando
select * from select_ramais


create or replace function inserir(
p_id_ram integer,
p_infos jsonb)
returns text
language 'plpgsql'
cost 100
as $body$
begin
if ( SELECT EXISTS (SELECT infos FROM ramais WHERE infos->>'nr_ramal' = p_infos->>'nr_rmal') ) then
return 'ja cadastrado no Banco';
else
insert into ramais (id_ram,infos) values (p_id_ram, p_infos);
return 'informacoes cadastradas com sucesso !!';
end if;
end;
$body$;

select *from inserir (
4,'{"nm_setor":"protocolo","nm_servidor":"Patrick", "nr_ramal": "2246"}'
)

Duas sugestões:

1 - Criar um campo só para o ramal na mesma tabela e colocar unique constraint;
2 - Em vez de Jsonb usar campos arrays do próprio Postgres, aqueles tipos de dados com [] ao lado, exemplo: date[], mas mesmo assim criar um campo só para o ramal com unique constraint;

Usando Json os dados ficam não-relacionais, ou seja, grosso modo, são um array com vários tipos de dados diferentes (um objeto, uma lista ordenada de valores).
Acredito que você teria que ver qual o tipo de dado do ramal no Jsonb para então tratá-lo na consulta e é aí que a porca torce o rabo, pois enquanto o json é guardado como uma cópia exata do texto JSON, o jsonb é armazenado numa forma binária decomposta, ou seja, grosso modo, Jsonb é armazenado no Postgres como código binário.

Não costumo trabalhar com Json no banco de dados, mas lembre-se:
A seta curta -> mantém o tipo como JSON, e a seta longa ->> retorna o texto.

Resumindo: acredito que é melhor você criar um campo só para o ramal dentro dessa tabela.
Porém, como o banco já está definido, acredito que isso é contraproducente para você fazer e talvez nem tenha como reconfigurar o banco pois os dados já estão cadastrados.

Mas aqui

https://www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/

acredito que tu encontrará boas sugestões para o que tu quer fazer em Jsonb com Postgres.


________________________________________________
Always listen the Buck!

Bom dia,

muito obrigada pela ajuda. Eu avancei bastante aqui na elaboração do banco, inserção de dados, criação das views e alguns select's que me retornam dados importantes.
Minha função ainda permanece com um erro, mas acredito que com alguns ajustes eu consigo meu objetivo.

Posta como está tua função agora, ou já conseguiu?


________________________________________________
Always listen the Buck!

Opa beleza?
retornei com atualizações da table, com views e uma função que entrega parte do resultado. Ela verifica ramal e servidor mas ainda não consigo fazer o update e o insert de novas as informações. Segue os scripts:


--objetivo geral:cadastro ou atualizacao de ramais e servidores em uma empresa
--objetivo especifico: não pode existir o mesmo numero de ramal em uma mesma filial mesmo que tenham servidores de nome diferentes,
--só é permitido ramais iguais em filiais diferentes ex:norte 2007, sul 2007

alter table policia rename to empresa
select *from empresa

--view que mostra a unidade e o telefone
create view sel_end as
select endereco->'nm_und' as unidade, endereco->'tel_und' as telefone from empresa where endereco->>'nm_und'='filial sul';

select *from sel_end

--view para ver os ramais das filiais
create view ramal_filial as select empresa.id_filial,
obj.value->>'nr_ramal'::text as ramal,
obj.value->>'nm_servidor'::text as servidor
from empresa,
lateral jsonb_array_elements(empresa.infos) obj(value);

select *from ramal_filial

alter table empresa rename id_div to id_filial

--view com as informacoes das filiais
create view filial as
select empresa.endereco->'nm_und'::text as filial,
empresa.endereco->'tel_und'::text as telefone,
empresa.endereco->'endereco'::text as endereco,
empresa.endereco->'email_und'::text as email from empresa

select *from filial

--funcao para verificar a existencia de ramais e servidores ja cadastrados, senão tiver atualize ou insira novas informações

create or replace function cadastro(
p_id_filial integer,
p_ender jsonb,
p_infos jsonb)
returns text
language'plpgsql'
cost 100
volatile parallel unsafe
as $BODY$
begin
--verifica se o ramal ja esta cadastrado
if((exists(select ramal from ramal_filial where to_jsonb(ramal)=p_infos->'nr_ramal')and
(exists(select id_filial from ramal_filial where id_filial=p_id_filial)))) then
return 'Ramal já cadastrado';
--verifica se ja existe aquele servidor
else if((exists(select servidor from ramal_filial where to_jsonb(servidor)=p_infos->'nm_servidor')and
(exists(select id_filial from ramal_filial where id_filial=p_id_filial)))) then
return 'Servidor já cadastrado';
--aqui ocorre a atualizacao das informações, realizando um update senão inserindo novo dado
else
if(exists(select id_filial from ramal_filial where id_filial=p_id_filial)) then
--
--
--
else
insert into empresa(id_filial,endereco,infos) values (p_id_filial,p_ender,p_infos);
end if;
return 'Informações cadastradas com sucesso';
end if;
end;
$BODY$;

No link
https://www.postgresql.org/docs/current/sql-createview.html

veja a seção Parameters (Parâmetros) logo no início

"WITH [ CASCADED | LOCAL ] CHECK OPTION
This option controls the behavior of automatically updatable views. When this option is specified, INSERT and UPDATE commands on the view will be checked to ensure that new rows satisfy the view-defining condition (that is, the new rows are checked to ensure that they are visible through the view). If they are not, the update will be rejected. If the CHECK OPTION is not specified, INSERT and UPDATE commands on the view are allowed to create rows that are not visible through the view. The following check options are supported:

LOCAL
New rows are only checked against the conditions defined directly in the view itself. Any conditions defined on underlying base views are not checked (unless they also specify the CHECK OPTION).

CASCADED
New rows are checked against the conditions of the view and all underlying base views. If the CHECK OPTION is specified, and neither LOCAL nor CASCADED is specified, then CASCADED is assumed.

The CHECK OPTION may not be used with RECURSIVE views.

Note that the CHECK OPTION is only supported on views that are automatically updatable, and do not have INSTEAD OF triggers or INSTEAD rules. If an automatically updatable view is defined on top of a base view that has INSTEAD OF triggers, then the LOCAL CHECK OPTION may be used to check the conditions on the automatically updatable view, but the conditions on the base view with INSTEAD OF triggers will not be checked (a cascaded check option will not cascade down to a trigger-updatable view, and any check options defined directly on a trigger-updatable view will be ignored). If the view or any of its base relations has an INSTEAD rule that causes the INSERT or UPDATE command to be rewritten, then all check options will be ignored in the rewritten query, including any checks from automatically updatable views defined on top of the relation with the INSTEAD rule."

e depois mais abaixo veja a seção Updatable Views.


E veja que aqui tu tem uma função e não uma view:
create or replace function cadastro


E aqui tu encontra exemplos de UPSERT com a cláusula ON CONFLICT, a partir da versão 9.5 do Postgres:

https://www.postgresql.org/docs/9.4/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPINGhttps://ww...

https://www.postgresql.org/docs/devel/sql-insert.html

Qualquer coisa usa a opção de tradução automática do Google Chrome ou o Google Tradutor.


Mas segue uma sugestão para o último bloco:

...

IF(exists(select id_filial from ramal_filial where id_filial=p_id_filial)) THEN
INSERT INTO empresa(id_filial,endereco,infos) values(p_id_filial,p_ender,p_infos);
ELSE
UPDATE empresa
SET nr_ramal = nr_ramal <<< aqui verifique se é isso mesmo
WHERE id_filial = p_id_filial
END IF;
RETURN 'Informações cadastradas com sucesso';
end;
$BODY$


________________________________________________
Always listen the Buck!