Ir para conteúdo
Fórum CódigoFonte.net
Entre para seguir isso  
Tiago Maia

Exemplo de FUNÇÃO que monta uma QUERY dinâmica e retorna várias colunas

Recommended Posts

Mais um exemplo legal de funções.

Neste exemplo vou mostrar uma FUNÇÃO que monta dinâmicamente uma QUERY e retorna o seu resultado em várias colunas.

Atenção aos parâmetros de entrada e saída. Atenção com o tipo de retorno da FUNÇÃO.

A QUERY é montada dentro de uma variável VARCHAR, portanto devemos ter atenção com a palavra reservada EXECUTE no FOR da FUNÇÃO antes de referenciar a variável com a QUERY.

Segue o código de exemplo:

CREATE OR REPLACE FUNCTION func_consultar_orcamentoanual(IN p_id INTEGER, 
                             IN p_estado VARCHAR, 
                             IN p_diretoria VARCHAR, 
                             IN p_ano INTEGER, 
                             OUT id INTEGER, 
                             OUT estado VARCHAR, 
                             OUT diretoria VARCHAR, 
                             OUT ano INTEGER) RETURNS SETOF record AS
$$
DECLARE
    v_cont INTEGER = 0;
    v_totalParametros INTEGER = 0;
    v_sql VARCHAR = '';
    v_condicao VARCHAR = '';
    
    resultado RECORD;
BEGIN
    ----- Conta os parâmetros -----
    IF p_id IS NOT NULL THEN
        v_totalParametros := v_totalParametros + 1;
    END IF;

    IF p_estado IS NOT NULL THEN
        v_totalParametros := v_totalParametros + 1;
    END IF;
    
    IF p_diretoria IS NOT NULL THEN
        v_totalParametros := v_totalParametros + 1;
    END IF;

    IF p_ano IS NOT NULL THEN
        v_totalParametros := v_totalParametros + 1;
    END IF;

    ----- Monta o WHERE da QUERY -----
    WHILE v_cont < v_totalParametros LOOP
        IF v_cont > 1 THEN
            v_condicao := v_condicao || ' AND ';
        END IF;

        IF p_id IS NOT NULL THEN
            v_condicao := v_condicao || 'id = ' || p_id;
            v_cont := v_cont + 1;

            IF v_cont < v_totalParametros THEN
                v_condicao := v_condicao || ' AND ';
            END IF;
        END IF;

        IF p_estado IS NOT NULL THEN
            v_condicao := v_condicao || 'estado = ' || '\'' || p_estado || '\'';
            v_cont := v_cont + 1;

            IF v_cont < v_totalParametros THEN
                v_condicao := v_condicao || ' AND ';
            END IF;
        END IF;

        IF p_diretoria IS NOT NULL THEN
            v_condicao := v_condicao || 'diretoria = ' || '\'' || p_diretoria || '\'';
            v_cont := v_cont + 1;

            IF v_cont < v_totalParametros THEN
                v_condicao := v_condicao || ' AND ';
            END IF;
        END IF;

        IF p_ano IS NOT NULL THEN
            v_condicao := v_condicao || 'ano = ' || p_ano;
            v_cont := v_cont + 1;

            IF v_cont < v_totalParametros THEN
                v_condicao := v_condicao || ' AND ';
            END IF;
        END IF;
    END LOOP;

    ----- Inicia a QUERY -----
    v_sql := 'SELECT * FROM tab_orcamentoAnual';

    ----- Tendo UM ou mais parâmetros, insere a cláusula WHERE na QUERY -----
    IF v_totalParametros > 0 THEN
        v_sql := v_sql || ' WHERE ';
    END IF;

    ----- Finaliza a QUERY -----
    v_sql := v_sql || v_condicao;

    ----- Executa a QUERY -----
    FOR resultado IN EXECUTE v_sql LOOP
        id := resultado.id;
        estado := resultado.estado;
        diretoria := resultado.diretoria;
        ano := resultado.ano;

        RETURN NEXT;
    END LOOP;
END;
$$ LANGUAGE 'plpgsql';

Abraço a todos!

- Tiago Maia

Compartilhar este post


Link para o post
Compartilhar em outros sites

Oi Tiago...

Li seu post e achei interessante pois cria uma função com retorno de colunas criadas na declaração da função. Mas, preciso de uma função um pouco mais dinâmica, pois não sei a quantidade de colunas e os nomes que preciso retornar. Isso vai depender da consulta realizada.

Seria mais ou menos assim:

select * from f_DRE('2013-09-01', '2013-09-05'): Nessa consulta precisaria das seguintes colunas de retorno:

plano_contas
2013-09-01
2013-09-02
2013-09-03
2013-09-04
2013-09-05

Sendo que os valores nas colunas, são consultas complexas obtendo soma de valores... Não sei por onde começo...
A função seria para criar um relatório de DRE, ou seja, fluxo de caixa previsto e realizado...

Você sabe se existe alguma forma de criar esse tipo de função?

Obrigada.

Compartilhar este post


Link para o post
Compartilhar em outros sites

Crie uma conta ou entre para comentar

Você precisar ser um membro para fazer um comentário

Criar uma conta

Crie uma nova conta em nossa comunidade. É fácil!

Crie uma nova conta

Entrar

Já tem uma conta? Faça o login.

Entrar Agora
Entre para seguir isso  

×
×
  • Criar Novo...