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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Visitante
Responder

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Processando...
Entre para seguir isso  

×
×
  • Criar Novo...