none
Select dentro de uma função no banco de dados RRS feed

  • Pergunta

  •  

    Pessoal, estou com problema e venho a recorrer a vcs... preciso de uma função que retorne um valor, porem o nome do campo e o nome da tablea serão dinamicos algo como o exemplo abaixo, não sei se a sintaxe esta correta, caso tenham alguma sugestão.. por favor... ps: postgre

     

     

    CREATE OR REPLACE FUNCTION RetornaCodigo(Coluna varchar ,Tabela varchar ) returns bigint AS
    $BODY$
    DECLARE

     

    Col ALIAS FOR $1;
    Tab ALIAS FOR $2;
    str record ;


    BEGIN

     

    str := "SELECT CASE MAX("|| Col ||") is null WHEN true THEN 1 ELSE MAX("|| Col ||") FORM " || Tab;

     

    return str;

     

    END;
     $BODY$
    LANGUAGE 'plpgsql' VOLATILE ;

     

    ps: o problema maior eh montar a query e executa-la dentro da função e assim rotornar o resultado.. talvez seja simples, porem meu baixo conhecimento com procidures e functions esta em barrando, procurei na net mas não achei algo que pudesse resolver este impasse.. mas grato desde ja...

     

    Att,

    Rafael Carneiro

    segunda-feira, 17 de dezembro de 2007 13:33

Respostas

  •  

    Rafael,

     

    Teste usar o sql_executesql, veja este site:

    http://www.sommarskog.se/dyn-search.html

     

    Tem uma descrição bem detalhada dessa proc, leia esse site primeiro e depois veja abaixo a resposta que dei a outro post:

     

     

     

    "No sp_executesql vc vai ter 3 tipos de parametros... vejamos:

    EXEC sp_executesql @param1, @param2, @paramN

     

    @param1 vai ser o teu select, digamos que quer encontrar todos clientes da cidade de São Paulo e estado SP, então dentro do @param1 vai ter algo parecido com isso:

    SELECT @param1 = 'SELECT codigoPessoa, nomePessoa

    FROM Pessoas

    WHERE Localidade = @XLocalidade AND UF = @XUF'

     

    @param2 vai ser a lista de parametros que poderão aparecer dentro de @param1, exemplo:

    SELECT @param2 = '@XLocalidade VARCHAR (100), @XUF CHAR (2)'

     

    E por final, @paramN são todos parametros que poderão ser usados, no exemplo, vou usar @Localidade e @UF. Veja como fica no final:

    EXEC sp_executesql @param1, @param2, @Localidade, @UF

     

    Deve ter notado que coloquei em muito lugares "pode" e "poderão" em relação os parâmetros usados no SQL... É exatamente aí que está o segredo, você pode manter o mesmo SELECT final, modificando apenas o @param1, exemplo completo disso:

     

    SELECT @param1 = 'SELECT codigoPessoa, nomePessoa

    FROM Pessoas

    WHERE UF = @XUF'

     

    SELECT @param2 = '@XLocalidade VARCHAR (100), @XUF CHAR (2)'

     

    EXEC sp_executesql @param1, @param2, @Localidade, @UF

     

     

    Neste caso o sp_executesql ficou exatamente igual!! O que mudou foi o conteúdo do @param1, para isso que serve aquele monte de IFs no início, pra montar o WHERE, somente com os parâmetros que foram passados com algum conteúdo."

     

     

     

    Abraço

    segunda-feira, 17 de dezembro de 2007 16:31

Todas as Respostas

  • Bom Dia Rafael,

     

    Entendi perfeitamente sua analogia com o Postgre, mas ao contrário do PgPL/SQL, o TSQL não aceitará construções dinâmicas dentro de uma function. A razão para isso é que construções dinâmicas são executadas com EXEC ou sp_executesql e essas construções não são permitidas dentro de Functions.

     

    Uma alternativa seria utilizar o OPENQUERY, mas isso deixaria sua solução um pouco mais complexa além de um RoundTrip a mais. Você teria que colocar o seguinte código dentro de sua function.

     

    SELECT * FROM OPENQUERY([Seu Servidor],'Sua Query Dinâmica')

     

    Para que isso funcione, você terá que permitir conexões remotas. Use:

     

    sp_serveroption 'data access', true

     

    [ ]s,

     

    Gustavo

     

    segunda-feira, 17 de dezembro de 2007 13:47
  • Grato pelo esclarecimento... Surgio uma uotra ipotese... tem como eu madar um SELETC ja montado so pra ser executado dentro da função.. como:

     

    CREATE OR REPLACE FUNCTION RetornaCodigo(strQuery varchar) returns bigint AS
    $BODY$
    DECLARE

     

    str record ;


    BEGIN

     

    str := strQuery

     

    return str;

     

    END;
     $BODY$
    LANGUAGE 'plpgsql' VOLATILE ;

     

    ... tem como executar uma string que eh recebida como paramentro nesta função....

     

    Grato desde ja,

     

    Att,

    Rafael Carneiro

    segunda-feira, 17 de dezembro de 2007 15:32
  •  

    Olá Rafael,

     

     

    Como havia sido dito pelo colega Gustavo, sempre que for necessário executar algum código dinâmico, é preciso usar um EXEC ou sp_executesql e estas instruções não são permitidas dentro de functions.

     

    Recomendo verificar a possibilidade de usar procedure no lugar de function.

     

    Veja se a própria sp_executesql atende suas necessidades.

     

     

    Abraço

    segunda-feira, 17 de dezembro de 2007 15:46
  • Rafael,

     

    Concordo plenamente com os colegas, em particular eu prefiro trabalhar com stored procedures, justamente por estas flexibilidades.

    segunda-feira, 17 de dezembro de 2007 16:02
  • Caro Junior Galvão, pode me citar um exemplo que resolva meu problema, de acordo com o relatado acima..., Grato desde de ja.

    segunda-feira, 17 de dezembro de 2007 16:08
  • Rafael,

     

    Você quer um exemplo, de um select dentro de uma procedure?

     

    segunda-feira, 17 de dezembro de 2007 16:24
  • posso ter entendido mal ... mas pelo q entendi com uma procedure ha como executar uma query dinamica????

    segunda-feira, 17 de dezembro de 2007 16:27
  •  

    Rafael,

     

    Teste usar o sql_executesql, veja este site:

    http://www.sommarskog.se/dyn-search.html

     

    Tem uma descrição bem detalhada dessa proc, leia esse site primeiro e depois veja abaixo a resposta que dei a outro post:

     

     

     

    "No sp_executesql vc vai ter 3 tipos de parametros... vejamos:

    EXEC sp_executesql @param1, @param2, @paramN

     

    @param1 vai ser o teu select, digamos que quer encontrar todos clientes da cidade de São Paulo e estado SP, então dentro do @param1 vai ter algo parecido com isso:

    SELECT @param1 = 'SELECT codigoPessoa, nomePessoa

    FROM Pessoas

    WHERE Localidade = @XLocalidade AND UF = @XUF'

     

    @param2 vai ser a lista de parametros que poderão aparecer dentro de @param1, exemplo:

    SELECT @param2 = '@XLocalidade VARCHAR (100), @XUF CHAR (2)'

     

    E por final, @paramN são todos parametros que poderão ser usados, no exemplo, vou usar @Localidade e @UF. Veja como fica no final:

    EXEC sp_executesql @param1, @param2, @Localidade, @UF

     

    Deve ter notado que coloquei em muito lugares "pode" e "poderão" em relação os parâmetros usados no SQL... É exatamente aí que está o segredo, você pode manter o mesmo SELECT final, modificando apenas o @param1, exemplo completo disso:

     

    SELECT @param1 = 'SELECT codigoPessoa, nomePessoa

    FROM Pessoas

    WHERE UF = @XUF'

     

    SELECT @param2 = '@XLocalidade VARCHAR (100), @XUF CHAR (2)'

     

    EXEC sp_executesql @param1, @param2, @Localidade, @UF

     

     

    Neste caso o sp_executesql ficou exatamente igual!! O que mudou foi o conteúdo do @param1, para isso que serve aquele monte de IFs no início, pra montar o WHERE, somente com os parâmetros que foram passados com algum conteúdo."

     

     

     

    Abraço

    segunda-feira, 17 de dezembro de 2007 16:31
  • Rafael,

     

    Veja bem, o que eu costumo fazer aqui na empresa é o seguinte, dentro das minhas aplicações, de acordo com a escolha do usuário eu monto o select!!!

     

     

     

    segunda-feira, 17 de dezembro de 2007 16:32
  • Muito obrigado Alexandre!!!

    segunda-feira, 17 de dezembro de 2007 16:36
  • Rafael,

     

    Consegui compreender o exemplo do Alexandre?

    segunda-feira, 17 de dezembro de 2007 16:41
  • Alexandre,

     

    Ótimo exemplo!!!

     

    Rafael,

     

    Quando você for declarar o primeiro @param1 é necessário especificar por exemplo o datatype que trabalhem com valores Unicode, pois a sp_executesql faz uso deste tipo, exemplo: NVarChar, NChar, NText...

    segunda-feira, 17 de dezembro de 2007 16:48
  •  

    Blz Rafael,

     

     

    Se precisar de uma mão, pode voltar aqui que lhe ajudamos.

     

     

     

    Abraço

    segunda-feira, 17 de dezembro de 2007 16:57