[Resolvido] Problemas com Stored Procedure

[fabiapo 0]

Pessoal,

 

Estou fazendo a seguinte procedure:

 

CREATE OR REPLACE FUNCTION login(login in varchar(32), senha in varchar(8), id_usuario out integer, id_perfil out integer)

RETURNS SETOF RECORD AS

$$

DECLARE

reg record;

BEGIN FOR reg IN EXECUTE

'SELECT A.id_usuario, R.id_perfil

from usuario_acesso A, perfil_usuario P, perfil_usuario_acesso R

where A.login = R.login AND P.id_perfil=R.id_perfil AND

A.nm_login='||login|| ' and sp_senha ='||senha

LOOP

id_usuario:= reg.id_usuario;

id_perfil:=reg.id_perfil;

RETURN NEXT;

END LOOP;

END;

$$

LANGUAGE plpgsql;

 

Ela é executada com sucesso. Porém, quando faço a chamada:

 

select ffp_login(abcd, pass)

ocorre o erro abaixo:

ERRO: coluna "pass" não existe no caracter 31

 

Se colocar aspas simples,

select ffp_login(login, 'pass')

 

Dá o erro:

ERRO: função login(bigint, unknown) não existe no caracter 8

DICA: Nenhuma função corresponde com o nome e os tipos de argumentos informados. Você precisa adicionar conversões de tipo explícitas.

 

Como faço para fazer esta conversão explícita no postgresql?

 

Obrigada!

[Danilo Passos 0]

O problema, ao que parece, é como você está passando os parâmetros para a função.

 

No select ffp_login(abcd, pass), tanto abcd e pass devem obrigatoriamente estar com apóstrofo (') por serem strings (ou varchar). Caso você não passe com apóstrofo, o banco entenderá que você está tentando passar uma varíavel ou uma coluna, daí a mensagem do primeiro erro.

 

Na sua segunda tentativa, select ffp_login(login, 'pass'), você fez certo no 'pass', mas faltou fazer o mesmo no login, pois, como disse, se você deseja passar uma string diretamente, é rpeciso o apóstrofo. Caso fosse uma variável, não precisaria.

 

Nota: Não existe aspas simples. Existe aspas ( " ) e apóstrofo ( ' ) :)

 

Pessoal,

 

Estou fazendo a seguinte procedure:

 

CREATE OR REPLACE FUNCTION login(login in varchar(32), senha in varchar(8), id_usuario out integer, id_perfil out integer)

RETURNS SETOF RECORD AS

$$

DECLARE

reg record;

BEGIN FOR reg IN EXECUTE

'SELECT A.id_usuario, R.id_perfil

from usuario_acesso A, perfil_usuario P, perfil_usuario_acesso R

where A.login = R.login AND P.id_perfil=R.id_perfil AND

A.nm_login='||login|| ' and sp_senha ='||senha

LOOP

id_usuario:= reg.id_usuario;

id_perfil:=reg.id_perfil;

RETURN NEXT;

END LOOP;

END;

$$

LANGUAGE plpgsql;

 

Ela é executada com sucesso. Porém, quando faço a chamada:

 

select ffp_login(abcd, pass)

ocorre o erro abaixo:

ERRO: coluna "pass" não existe no caracter 31

 

Se colocar aspas simples,

select ffp_login(login, 'pass')

 

Dá o erro:

ERRO: função login(bigint, unknown) não existe no caracter 8

DICA: Nenhuma função corresponde com o nome e os tipos de argumentos informados. Você precisa adicionar conversões de tipo explícitas.

 

Como faço para fazer esta conversão explícita no postgresql?

 

Obrigada!

[fabiapo 0]

Danilo,

 

Eu fiz este teste colocando apóstofro, mas daí dá o seguinte erro:

 

select login('1234', 'pass')

 

ERRO: operador não existe: character varying = bigint no caracter 186

DICA: Nenhum operador corresponde com o nome e o(s) tipo(s) de argumento(s) informados. Você precisa adicionar conversões de tipo explícitas.

CONSULTA: SELECT A.id_usuario, R.id_perfil

from usuario_acesso A, perfil_usuario P, perfil_usuario_acesso R

where A.nm_login = R.nm_login AND P.id_perfil=R.id_perfil AND

A.nm_login=1234 and sp_senha =pass

CONTEXTO: PL/pgSQL function "login" line 3 at FOR over EXECUTE statement

[Danilo Passos 0]

Fabiapo,

 

Isso porque nessa linha:

 

where A.login = R.login AND P.id_perfil=R.id_perfil AND

A.nm_login='||login|| ' and sp_senha ='||senha

 

Você está pegando o conteúdo da varíavel login (1234) e sp_senha (pass) e concatenando (usando o ||) diretamente na sua consulta SQL.

 

Dessa forma, ao passar 1234 como login na sua consulta, ele interpreta 1234 como sendo um número, e não um varchar, dando esse erro.

 

O que recomendo você a fazer é não concatenar o conteúdo da variável, mas sim utilizá-la diretamente.

 

where A.login = R.login AND P.id_perfil=R.id_perfil AND

A.nm_login = login and sp_senha = senha

 

Assim, o banco verifica o tipo da variável (varchar) e ao comparar, faz de forma correta.

 

Danilo,

 

Eu fiz este teste colocando apóstofro, mas daí dá o seguinte erro:

 

select login('1234', 'pass')

 

ERRO: operador não existe: character varying = bigint no caracter 186

DICA: Nenhum operador corresponde com o nome e o(s) tipo(s) de argumento(s) informados. Você precisa adicionar conversões de tipo explícitas.

CONSULTA: SELECT A.id_usuario, R.id_perfil

from usuario_acesso A, perfil_usuario P, perfil_usuario_acesso R

where A.nm_login = R.nm_login AND P.id_perfil=R.id_perfil AND

A.nm_login=1234 and sp_senha =pass

CONTEXTO: PL/pgSQL function "login" line 3 at FOR over EXECUTE statement

[fabiapo 0]

Danilo,

 

O grande problema desta consulta é que tenho dois parâmetros.

Como para definir o SELECT eu preciso colocar apostrofo, quando eu não concateno aparece a seguinte mensagem:

 

ERRO: erro de sintaxe em ou próximo a "$1" no caracter 198

CONSULTA: SELECT 'SELECT A.id_usuario, R.id_perfil

from usuario_acesso A, perfil_usuario P, perfil_usuario_acesso R

where A.nm_login = R.nm_login AND P.id_perfil=R.id_perfil AND

A.nm_login=' $1 ' and sp_senha =' $2

CONTEXTO: SQL statement in PL/PgSQL function "login" near line 7

 

Segue a alteração que fiz:

 

CREATE OR REPLACE FUNCTION ffp_login(cpf in varchar(32), senha in varchar(8), id_usuario out integer, id_perfil out integer)

RETURNS SETOF RECORD AS

$$

DECLARE

reg record;

BEGIN FOR reg IN EXECUTE

'SELECT A.id_usuario, R.id_perfil

from usuario_acesso A, perfil_usuario P, perfil_usuario_acesso R

where A.nm_login = R.nm_login AND P.id_perfil=R.id_perfil AND

A.nm_login='cpf ' and sp_senha ='senha

LOOP

id_usuario:= reg.id_usuario;

id_perfil:=reg.id_perfil;

RETURN NEXT;

END LOOP;

END;

$$

LANGUAGE plpgsql;

 

Se coloco as variáveis dentro do apostrofo, não dá certo, assim como se colocarmos $1, $2 dentro dos apostrofos pois ele interpreta como um valor pré-definido. Se não colocarmos os apostrofos, não roda o sql de create.

[Danilo Passos 0]

O seu FOR usa o execute, mas não é preciso. O execute é apenas quando você quer fazer com que uma string seja executa como uma query. Nesse caso não é preciso.

 

Faça:

 

BEGIN 
   FOR reg IN 
		SELECT A.id_usuario, R.id_perfil 
		from usuario_acesso A, perfil_usuario P, perfil_usuario_acesso R 
		where A.nm_login = R.nm_login AND P.id_perfil=R.id_perfil AND 
		A.nm_login = cpf and sp_senha = senha
   LOOP

Dentro da stored procedure você pode misturar um select, insert, etc com variáveis, seja da própia sp ou que venha como parametro.

[fabiapo 0]

Danilo,

 

Retirei o execute e as apóstrofes e mesmo assim ocorre o erro:

 

ERRO: operador não existe: character varying = bigint no caracter 186

DICA: Nenhum operador corresponde com o nome e o(s) tipo(s) de argumento(s) informados. Você precisa adicionar conversões de tipo explícitas.

CONSULTA: SELECT A.id_usuario, R.id_perfil

from usuario_acesso A, perfil_usuario P, perfil_usuario_acesso R

where A.nm_login = R.nm_login AND P.id_perfil=R.id_perfil AND

A.nm_login=teste and sp_senha =pass

CONTEXTO: PL/pgSQL function "login" line 3 at FOR over EXECUTE statement

 

Se executarmos este SELECT do resultado, aparece a mesma mensagem de erro.

 

Quando coloco apóstrofo nos parâmetros do SQL funciona.

 

Será que tenho que realizar o declare dos valores antes de executar o SELECT?

[fabiapo 0]

Danilo,

 

Fiz o seguinte procedimento:

 

Troquei a declaração de login e senha de VARCHAR para TEXT e funcionou.

 

Ficou assim:

 

CREATE OR REPLACE FUNCTION login(login text, senha text, id_usuario out integer, id_perfil out integer)

RETURNS SETOF RECORD AS

$$

DECLARE

reg record;

BEGIN FOR reg IN

SELECT A.id_usuario, R.id_perfil

from usuario_acesso A, perfil_usuario P, perfil_usuario_acesso R

where A.nm_login = R.nm_login AND P.id_perfil=R.id_perfil AND

A.nm_login=cpf and sp_senha =senha

LOOP

id_usuario:= reg.id_usuario;

id_perfil:=reg.id_perfil;

RETURN NEXT;

END LOOP;

END;

$$

LANGUAGE plpgsql;

 

Muito obrigada pela ajuda!

[Danilo Passos 0]

Fabiapo,

 

provavelmente, a sua tabela difere dos parametros passados.

 

Observe que na sua declaração do stored procedure tem:

 

login in varchar(32), senha in varchar(8) (Nota: mude o nome da variável login para algo diferente, já que a funcao tb é login, para n confudir, use v_login e v_senha, assim você sabe que é uma variável da stored procedure).

 

Voltando:

login in varchar(32), senha in varchar(8)

 

Agora, olhe sua tabela e verifique se A.nm_login e sp_senha são iguais ao do tipo passado como parâmetro. Possivelmente, uma dessas duas esta definida como bigint, causando o problema.

 

Dessa forma, ou você muda stored procedure para ficar igual a tabela, ou o contrário.

 

Possivelmente, o porque de funcionar quando você usa o execute se dá pelo fato de não haver apóstrofos, o que carecteriza uma string/varchar, fazendo com que ele interprete como um bigint. Se você tentar passar como parâmetro para stored proceudre números e letras, provavelmente dará errado com o excute.

 

[pausa pro almoço, volto em 1h >P]

[fabiapo 0]

Danilo,

 

A minha tabela está com:

 

login - varchar(50)

senha - varchar(8)

 

Até tinha colocado varchar devido a estrutura da tabela.

 

Achei estranho ele interpretar como int. Com text ele converte automaticamente.

[Danilo Passos 0]

Bom, se ainda tiver ocorrendo, cole a stored procedure + estruturas da tabelas aqui para ver o que mais pode ser.

[fabiapo 0]

Danilo,

 

Seguem as tabelas:

 

usuario_acesso A:

nm_login varchar(50) NOT NULL

sp_senha varchar(8)

id_usuario int4 NOT NULL

perfil_usuario P:

id_perfil int4 NOT NULL

ds_perfil varchar(32)

 

perfil_usuario_acesso R:

id_perfil int4 NOT NULL

nm_login varchar(50) NOT NULL

 

SP:

CREATE OR REPLACE FUNCTION ffp_login(vlogin text, senha text, id_usuario out integer, id_perfil out integer)

RETURNS SETOF RECORD AS

$$

DECLARE

reg record;

BEGIN FOR reg IN

SELECT A.id_usuario, R.id_perfil

from usuario_acesso A, perfil_usuario P, perfil_usuario_acesso R

where A.nm_login = R.nm_login AND P.id_perfil=R.id_perfil AND

A.nm_login=vlogin and sp_senha =senha

LOOP

id_usuario:= reg.id_usuario;

id_perfil:=reg.id_perfil;

RETURN NEXT;

END LOOP;

END;

$$

LANGUAGE plpgsql;

[Danilo Passos 0]

Fabiapo, bom, deveria funcionar mesmo com varchar com parâmetro, mas se estiver funcionando com text, beleza, menos mal e já serve para o que você quer ;) Acredito que não haverá mais erros. Só daria errado se você ainda usasse o execute :)

 

Bom, acredito que é isso, não há mais erro né? Que bom que a ajudei :)

 

Qualquer dúvida, só mandar.

[fabiapo 0]

Danilo,

 

Muito obrigada!