date:20140613

[pgbr-geral] ajuda com funcao

2014-06-13 Por tôpico Eloi Ribeiro


Ola a todos os participantes da lista,

Gostaria de fazer uma função que:
 1. verifica-se se um determinado valor já existe numa tabela
 2. se sim, que me devolva o id desse registo
 3. se não, que faca o insert e em seguida devolva o id resultante

Escrevi a seguinte código mas falta-me saber como recuperar o resultado 
do EXECUTE. Alguém me pode ajudar? Obrigado.


CREATE OR REPLACE FUNCTION Attribute.fun_insert_attribute(name text, 
description text, type text)

  RETURNS text AS
$BODY$
DECLARE
attribute_id integer;
n integer;
BEGIN
-- verifica se já existe
 EXECUTE 'SELECT count(*) FROM Attribute.Attribute WHERE Name 
ILIKE '||name||';'

-- se já existe, conhecer o id
IF n  1 THEN
 EXECUTE 'SELECT AttributeId FROM Attribute.Attribute WHERE 
Name ILIKE '||name||';'

-- se não existe, inserir e conhecer o id
ELSE
 EXECUTE 'INSERT INTO 
Attribute.Attribute(Name,Description,version_type) 
VALUES('||name||','||description||','||type||') RETURNING 
AttributeId;'

END IF;
RETURN attribute_id;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Os melhores cumprimentos,

--
Eloi
___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

Re: [pgbr-geral] ajuda com funcao

2014-06-13 Por tôpico Matheus de Oliveira

2014-06-13 6:39 GMT-03:00 Eloi Ribeiro e...@openmailbox.org:

 Gostaria de fazer uma função que:
  1. verifica-se se um determinado valor já existe numa tabela
  2. se sim, que me devolva o id desse registo
  3. se não, que faca o insert e em seguida devolva o id resultante

 Escrevi a seguinte código mas falta-me saber como recuperar o resultado do
 EXECUTE. Alguém me pode ajudar? Obrigado.


Em primeiro lugar, você não precisaria do EXECUTE, basta executar os
comandos SQL diretamente. No seu caso o EXECUTE não é só ruim, está
**errado**, pois não trata corretamente escape e está suscetível à
SQL-injection.

Agora, se quer recuperar o valor, mas caso não tenha inserir, basta usar a
variável FOUND (ainda não está bom, veja as considerações abaixo):

SELECT AttributeId INTO n
FROM Attribute.Attribute
WHERE lower(Name) LIKE lower(name);
-- se não existe, inserir e conhecer o id
IF NOT FOUND THEN
INSERT INTO Attribute.Attribute(Name, Description,
version_type)
VALUES(name, description, type)
RETURNING AttributeId INTO n;
END IF;
RETURN n;

Agora, essa função apesar de **aparentemente** fazer o que você quer, ainda
tem diversos possíveis problemas:

1. Você busca usando ILIKE (até troquei por lower+LIKE, por ser mais
eficiente), mas o que acontece se retornar mais de um registro? Poderia
acontecer? No caso acima o PG irá pegar um deles arbitrariamente (na sorte
mesmo). Será que você não queria ter usando igualdade? O campo Name é
único (tem chave UNIQUE)?
2. A função não está tratando concorrência. Imagine o que acontece se você
fizer o SELECT, não retornar nada, mas quando for fazer um INSERT alguém
acabou de um Name que casaria. Se Name for UNIQUE vai dar um problemão;
se não, só vai ser estranho e acho que você deveria repensar seriamente no
seu modelo de dados e suas operações (me cheira mal, mas tenho pouca
informação para afirmar).
3. Por que está usando nomes de tabelas e atributos com aspas duplas? Não
acha que fica horrível de usar assim depois? (esse não é um problema de
fato, só uma prática ruim na minha opinião).

Atenciosamente,
-- 
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

Re: [pgbr-geral] ajuda com funcao

2014-06-13 Por tôpico Eloi Ribeiro


On 2014-06-13 12:02, Matheus de Oliveira wrote:

2014-06-13 6:39 GMT-03:00 Eloi Ribeiro e...@openmailbox.org:



Em primeiro lugar, você não precisaria do EXECUTE, basta executar os
comandos SQL diretamente. No seu caso o EXECUTE não é só ruim,
está **errado**, pois não trata corretamente escape e está
suscetível à SQL-injection.


Obrigado, isso era o que me faltava saber.


Agora, se quer recuperar o valor, mas caso não tenha inserir, basta
usar a variável FOUND (ainda não está bom, veja as considerações
abaixo):


Muito melhor a solução que apresentas, poupo um passo.



Agora, essa função apesar de **aparentemente** fazer o que você
quer, ainda tem diversos possíveis problemas:

1. Você busca usando ILIKE (até troquei por lower+LIKE, por ser mais
eficiente), mas o que acontece se retornar mais de um registro?
Poderia acontecer? No caso acima o PG irá pegar um deles
arbitrariamente (na sorte mesmo). Será que você não queria ter
usando igualdade? O campo Name é único (tem chave UNIQUE)?


Tem um UNIQUE, não existem valores repetidos.


2. A função não está tratando concorrência. Imagine o que
acontece se você fizer o SELECT, não retornar nada, mas quando for
fazer um INSERT alguém acabou de um Name que casaria. Se Name for
UNIQUE vai dar um problemão; se não, só vai ser estranho e acho que
você deveria repensar seriamente no seu modelo de dados e suas
operações (me cheira mal, mas tenho pouca informação para
afirmar).


Não deveria passar porque nesta tabela só podem inserir valores muito 
pouca gente. Mas terei cuidado com isso.



3. Por que está usando nomes de tabelas e atributos com aspas duplas?
Não acha que fica horrível de usar assim depois? (esse não é um
problema de fato, só uma prática ruim na minha opinião).


Eu também não gosto nada e da-me muitas dores de cabeça quando tenho de 
escrever código em Python. Pois todo o banco de dados, todos os objetos 
estão em PascalCase, uma loucura. Cada vez que tenho de por uma aspa 
() tenho de pressionar 3 teclas! E não imaginas o que me custou de 
convencer-lhes de que isto não é o mais ótimo. Mas se fosse só isso...


A função ficou assim:

CREATE OR REPLACE FUNCTION Attribute.fun_insert_attribute(name text, 
description text, type text)

  RETURNS text AS
$BODY$
DECLARE
attribute_id integer;
BEGIN
-- verifica se ja existe e devolve o id
SELECT AttributeId INTO attribute_id FROM Attribute.Attribute 
WHERE lower(trim(Name)) LIKE lower(trim(name));

-- se nao existe, inserir e conhecer o id
IF NOT FOUND THEN
 INSERT INTO 
Attribute.Attribute(Name,Description,version_type) 
VALUES(trim(name),trim(description),trim(type)) RETURNING AttributeId 
INTO attribute_id;

END IF;
RETURN attribute_id;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


--
Eloi
___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

[pgbr-geral] Concatenação de Strings

2014-06-13 Por tôpico Junior Miranda

Boa noite!

Criei uma função que retorna RECORD (postgresql). Dentro desta função
possuo uma concatenação do caractere de vazio com um campo.
Ex.
Código:

return query select
field01,
field02,
'  ' || field03 as field03
   from campo
   order by field01;


A função é comitada sem problema algum, mas quando realizo a consulta,
retorna o erro
Ex.
Código:

select field01,
  field02,
  field03
from funcao()
as Tabela(field01  integer, field02  varchar(6), field03 character
varying(38))


ERRO: structure of query does not match function result type
DETAIL: Tipo text retornado não corresponde ao tipo character varying(3Cool
esperado na coluna 3.
CONTEXT: PL/pgSQL function fn_sisbusca_grupos() line 17 at RETURN QUERY

** Error **
ERRO: structure of query does not match function result type
SQL state: 42804

Já usei CONCAT, também, e tenho o mesmo erro. A concatenação está
retornando um tipo de dado diferente do esperado. Alguém já passou por
situação parecida??

*Analista de Sistemas*
*Especializando em Sistemas Computacionais*
*E-mail: flmirandajun...@gmail.com flmirandajun...@gmail.com*
*Tel.: *(75) 9191-1678/ 34143042/ 34143149/ 34143020
___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

Re: [pgbr-geral] Concatenação de Strings

2014-06-13 Por tôpico Rogério Ferreira

Vc vai ter q usar case when e testar o valor nulo, nulo e um tipo toda
operaçao com nulo vc tem que trasformar para o tipo, no seu caso acho que
um texto vazio, para somas e normal transformar em zero e por ai vai
Em 13/06/2014 19:44, Junior Miranda flmirandajun...@gmail.com escreveu:

 Boa noite!

 Criei uma função que retorna RECORD (postgresql). Dentro desta função
 possuo uma concatenação do caractere de vazio com um campo.
 Ex.
 Código:

 return query select
 field01,
 field02,
 '  ' || field03 as field03
from campo
order by field01;


 A função é comitada sem problema algum, mas quando realizo a consulta,
 retorna o erro
 Ex.
 Código:

 select field01,
   field02,
   field03
 from funcao()
 as Tabela(field01  integer, field02  varchar(6), field03 character
 varying(38))


 ERRO: structure of query does not match function result type
 DETAIL: Tipo text retornado não corresponde ao tipo character
 varying(3Cool esperado na coluna 3.
 CONTEXT: PL/pgSQL function fn_sisbusca_grupos() line 17 at RETURN QUERY

 ** Error **
 ERRO: structure of query does not match function result type
 SQL state: 42804

 Já usei CONCAT, também, e tenho o mesmo erro. A concatenação está
 retornando um tipo de dado diferente do esperado. Alguém já passou por
 situação parecida??

 *Analista de Sistemas*
 *Especializando em Sistemas Computacionais*
 *E-mail: flmirandajun...@gmail.com flmirandajun...@gmail.com*
 *Tel.: *(75) 9191-1678/ 34143042/ 34143149/ 34143020


 ___
 pgbr-geral mailing list
 pgbr-geral@listas.postgresql.org.br
 https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral


___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

Re: [pgbr-geral] Concatenação de Strings

2014-06-13 Por tôpico Junior Miranda

Obrigado pela ajuda Rogério!! Não se compreendi muito bem... Você quis
dizer que no select à função, eu devo fazer um CASE WHEN, e um COALESCE
para o cast do valores nulos?? A minha idéia seria justamente trazer o
valor com espaços a esquerda. Tipo:

'   Junior'

Júnior Miranda
*Analista de Sistemas*
*Especializando em Sistemas Computacionais*
*E-mail: flmirandajun...@gmail.com flmirandajun...@gmail.com*
*Tel.: *(75) 9191-1678/ 34143042/ 34143149/ 34143020



Em 13 de junho de 2014 20:48, Rogério Ferreira rogeriofsan...@gmail.com
escreveu:

 Vc vai ter q usar case when e testar o valor nulo, nulo e um tipo toda
 operaçao com nulo vc tem que trasformar para o tipo, no seu caso acho que
 um texto vazio, para somas e normal transformar em zero e por ai vai
 Em 13/06/2014 19:44, Junior Miranda flmirandajun...@gmail.com
 escreveu:

 Boa noite!

 Criei uma função que retorna RECORD (postgresql). Dentro desta função
 possuo uma concatenação do caractere de vazio com um campo.
 Ex.
 Código:

 return query select
 field01,
 field02,
 '  ' || field03 as field03
from campo
order by field01;


 A função é comitada sem problema algum, mas quando realizo a consulta,
 retorna o erro
 Ex.
 Código:

 select field01,
   field02,
   field03
 from funcao()
 as Tabela(field01  integer, field02  varchar(6), field03 character
 varying(38))


 ERRO: structure of query does not match function result type
 DETAIL: Tipo text retornado não corresponde ao tipo character
 varying(3Cool esperado na coluna 3.
 CONTEXT: PL/pgSQL function fn_sisbusca_grupos() line 17 at RETURN QUERY

 ** Error **
 ERRO: structure of query does not match function result type
 SQL state: 42804

 Já usei CONCAT, também, e tenho o mesmo erro. A concatenação está
 retornando um tipo de dado diferente do esperado. Alguém já passou por
 situação parecida??

 *Analista de Sistemas*
 *Especializando em Sistemas Computacionais*
 *E-mail: flmirandajun...@gmail.com flmirandajun...@gmail.com*
 *Tel.: *(75) 9191-1678/ 34143042/ 34143149/ 34143020


 ___
 pgbr-geral mailing list
 pgbr-geral@listas.postgresql.org.br
 https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral


 ___
 pgbr-geral mailing list
 pgbr-geral@listas.postgresql.org.br
 https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral


___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

Re: [pgbr-geral] Concatenação de Strings

2014-06-13 Por tôpico Matheus de Oliveira

2014-06-13 19:44 GMT-03:00 Junior Miranda flmirandajun...@gmail.com:

 return query select
 field01,
 field02,
 '  ' || field03 as field03
from campo
order by field01;


 A função é comitada sem problema algum, mas quando realizo a consulta,
 retorna o erro
 Ex.
 Código:

 select field01,
   field02,
   field03
 from funcao()
 as Tabela(field01  integer, field02  varchar(6), field03 character
 varying(38))


 ERRO: structure of query does not match function result type


O motivo é porque quando você concatena valores o resultado que você tem é
text ou varchar sem precisão, daí teria que chamar da seguinte forma:

select field01,
  field02,
  field03
from funcao()
as Tabela(field01  integer, field02  varchar(6), field03 text);

De qualquer forma. A não ser que sua função tenha realmente um retorno
dinâmico, eu recomendaria evitar o RETURNS [SETOF] RECORD, e usar RETURNS
TABLE, parâmetros OUT ou tipos definidos. Facilita a vida e evita esse tipo
de problema.

Atenciosamente,
-- 
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

Re: [pgbr-geral] Concatenação de Strings

2014-06-13 Por tôpico Rogério Ferreira

Se o meu teor alcoolico deixar, o codigo ficaria algo parecido com isso

return query select
field01,
field02,
'  ' || case when field03 is null then '' else
field03 end as field03
   from campo
   order by field01;
Em 13/06/2014 21:02, Junior Miranda flmirandajun...@gmail.com escreveu:

 Se não for incômodo, poderia exemplificar?

 Júnior Miranda
 *Analista de Sistemas*
 *Especializando em Sistemas Computacionais*
 *E-mail: flmirandajun...@gmail.com flmirandajun...@gmail.com*
 *Tel.: *(75) 9191-1678/ 34143042/ 34143149/ 34143020



 Em 13 de junho de 2014 20:57, Junior Miranda flmirandajun...@gmail.com
 escreveu:

 Obrigado pela ajuda Rogério!! Não se compreendi muito bem... Você quis
 dizer que no select à função, eu devo fazer um CASE WHEN, e um COALESCE
 para o cast do valores nulos?? A minha idéia seria justamente trazer o
 valor com espaços a esquerda. Tipo:

 '   Junior'

 Júnior Miranda
 *Analista de Sistemas*
 *Especializando em Sistemas Computacionais*
 *E-mail: flmirandajun...@gmail.com flmirandajun...@gmail.com*
 *Tel.: *(75) 9191-1678/ 34143042/ 34143149/ 34143020



 Em 13 de junho de 2014 20:48, Rogério Ferreira rogeriofsan...@gmail.com
 escreveu:

 Vc vai ter q usar case when e testar o valor nulo, nulo e um tipo toda
 operaçao com nulo vc tem que trasformar para o tipo, no seu caso acho que
 um texto vazio, para somas e normal transformar em zero e por ai vai
 Em 13/06/2014 19:44, Junior Miranda flmirandajun...@gmail.com
 escreveu:

  Boa noite!

 Criei uma função que retorna RECORD (postgresql). Dentro desta função
 possuo uma concatenação do caractere de vazio com um campo.
 Ex.
 Código:

 return query select
 field01,
 field02,
 '  ' || field03 as field03
from campo
order by field01;


 A função é comitada sem problema algum, mas quando realizo a consulta,
 retorna o erro
 Ex.
 Código:

 select field01,
   field02,
   field03
 from funcao()
 as Tabela(field01  integer, field02  varchar(6), field03 character
 varying(38))


 ERRO: structure of query does not match function result type
 DETAIL: Tipo text retornado não corresponde ao tipo character
 varying(3Cool esperado na coluna 3.
 CONTEXT: PL/pgSQL function fn_sisbusca_grupos() line 17 at RETURN QUERY

 ** Error **
 ERRO: structure of query does not match function result type
 SQL state: 42804

 Já usei CONCAT, também, e tenho o mesmo erro. A concatenação está
 retornando um tipo de dado diferente do esperado. Alguém já passou por
 situação parecida??

 *Analista de Sistemas*
 *Especializando em Sistemas Computacionais*
 *E-mail: flmirandajun...@gmail.com flmirandajun...@gmail.com*
 *Tel.: *(75) 9191-1678/ 34143042/ 34143149/ 34143020


 ___
 pgbr-geral mailing list
 pgbr-geral@listas.postgresql.org.br
 https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral


 ___
 pgbr-geral mailing list
 pgbr-geral@listas.postgresql.org.br
 https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral




 ___
 pgbr-geral mailing list
 pgbr-geral@listas.postgresql.org.br
 https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral


___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

Re: [pgbr-geral] Concatenação de Strings

2014-06-13 Por tôpico Junior Miranda

Obrigado pela atenção, Rogério e Matheus!!

Vou testar as sugestões e posto os resultados, logo mais...



Júnior Miranda
*Analista de Sistemas*
*Especializando em Sistemas Computacionais*
*E-mail: flmirandajun...@gmail.com flmirandajun...@gmail.com*
*Tel.: *(75) 9191-1678/ 34143042/ 34143149/ 34143020



Em 13 de junho de 2014 21:06, Rogério Ferreira rogeriofsan...@gmail.com
escreveu:

 Se o meu teor alcoolico deixar, o codigo ficaria algo parecido com isso

 return query select
 field01,
 field02,
 '  ' || case when field03 is null then '' else
 field03 end as field03

from campo
order by field01;

 Em 13/06/2014 21:02, Junior Miranda flmirandajun...@gmail.com
 escreveu:

 Se não for incômodo, poderia exemplificar?

 Júnior Miranda
 *Analista de Sistemas*
 *Especializando em Sistemas Computacionais*
 *E-mail: flmirandajun...@gmail.com flmirandajun...@gmail.com*
 *Tel.: *(75) 9191-1678/ 34143042/ 34143149/ 34143020



 Em 13 de junho de 2014 20:57, Junior Miranda flmirandajun...@gmail.com
 escreveu:

 Obrigado pela ajuda Rogério!! Não se compreendi muito bem... Você quis
 dizer que no select à função, eu devo fazer um CASE WHEN, e um COALESCE
 para o cast do valores nulos?? A minha idéia seria justamente trazer o
 valor com espaços a esquerda. Tipo:

 '   Junior'

 Júnior Miranda
 *Analista de Sistemas*
 *Especializando em Sistemas Computacionais*
 *E-mail: flmirandajun...@gmail.com flmirandajun...@gmail.com*
 *Tel.: *(75) 9191-1678/ 34143042/ 34143149/ 34143020



 Em 13 de junho de 2014 20:48, Rogério Ferreira rogeriofsan...@gmail.com
  escreveu:

 Vc vai ter q usar case when e testar o valor nulo, nulo e um tipo toda
 operaçao com nulo vc tem que trasformar para o tipo, no seu caso acho que
 um texto vazio, para somas e normal transformar em zero e por ai vai
 Em 13/06/2014 19:44, Junior Miranda flmirandajun...@gmail.com
 escreveu:

  Boa noite!

 Criei uma função que retorna RECORD (postgresql). Dentro desta função
 possuo uma concatenação do caractere de vazio com um campo.
 Ex.
 Código:

 return query select
 field01,
 field02,
 '  ' || field03 as field03
from campo
order by field01;


 A função é comitada sem problema algum, mas quando realizo a consulta,
 retorna o erro
 Ex.
 Código:

 select field01,
   field02,
   field03
 from funcao()
 as Tabela(field01  integer, field02  varchar(6), field03 character
 varying(38))


 ERRO: structure of query does not match function result type
 DETAIL: Tipo text retornado não corresponde ao tipo character
 varying(3Cool esperado na coluna 3.
 CONTEXT: PL/pgSQL function fn_sisbusca_grupos() line 17 at RETURN
 QUERY

 ** Error **
 ERRO: structure of query does not match function result type
 SQL state: 42804

 Já usei CONCAT, também, e tenho o mesmo erro. A concatenação está
 retornando um tipo de dado diferente do esperado. Alguém já passou por
 situação parecida??

 *Analista de Sistemas*
 *Especializando em Sistemas Computacionais*
 *E-mail: flmirandajun...@gmail.com flmirandajun...@gmail.com*
 *Tel.: *(75) 9191-1678/ 34143042/ 34143149/ 34143020


 ___
 pgbr-geral mailing list
 pgbr-geral@listas.postgresql.org.br
 https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral


 ___
 pgbr-geral mailing list
 pgbr-geral@listas.postgresql.org.br
 https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral




 ___
 pgbr-geral mailing list
 pgbr-geral@listas.postgresql.org.br
 https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral


 ___
 pgbr-geral mailing list
 pgbr-geral@listas.postgresql.org.br
 https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral


___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

[pgbr-geral] ajuda com funcao

Re: [pgbr-geral] ajuda com funcao

Re: [pgbr-geral] ajuda com funcao

[pgbr-geral] Concatenação de Strings

Re: [pgbr-geral] Concatenação de Strings

Re: [pgbr-geral] Concatenação de Strings

Re: [pgbr-geral] Concatenação de Strings

Re: [pgbr-geral] Concatenação de Strings

Re: [pgbr-geral] Concatenação de Strings

9 matches

Site Navigation

Mail list logo

Footer information