sqlpostgresqlset-returning-functions

Convert function and type Oracle to Postgres


I have a function and 2 types in Oracle, where a type I already did the conversion, put the function and another type I didn't find the best way to translate.

The purpose of this function is to be like a 'table' for a from of a select. How to select * from myfunction().

What I'm really struggling with is how to return a table type, which has been defined as a type. I think of this line of solution so as not to make too many changes

Type 1, already converted.

CREATE TYPE softbus.t_servico_fretamento_viagens AS
 (EMPRESA         numeric,  
 SERVICO          numeric,  
 TIPO             CHAR(10),  
 ITEM             numeric,  
 CLIENTE          numeric,  
 TIPO_VEI         numeric,  
 EMPDESTINO       numeric,  
 DESTINO          numeric,  
 PONTOORIGEM      numeric,  
 PONTODESTINO     numeric,  
 CATEGORIA        numeric,  
 EMPROTA          numeric,  
 ROTA             CHAR(4),  
 DATASAIDA        numeric,  
 HORASAIDA        numeric,  
 DATACHEGADA      numeric,  
 HORACHEGADA      numeric,  
 LOCALEMBARQUE    VARCHAR(200),  
 LOCALDESEMBARQUE VARCHAR(200),  
 MOTORISTA1       numeric,  
 MOTORISTA2       numeric,  
 VENDEDOR         numeric,  

Type 2, not converted.

CREATE TYPE T_SERVICO_FRETA_VIAGENS_TABELA AS TABLE OF T_SERVICO_FRETAMENTO_VIAGENS;

Function, not converted.

create or replace
FUNCTION FUNCAO_BUSCA_FRETA_VIAGENS 
(pEMPRESA IN NUMBER, pDATAINICIAL IN NUMBER, pDATAFINAL IN NUMBER, pRETPERIODO IN CHAR, pRETCANC IN CHAR) RETURN T_SERVICO_FRETA_VIAGENS_TABELA IS 
/*(EMPRESA          NUMBER(10,0), 
 SERVICO          NUMBER(10,0), 
 TIPO             CHAR(10), 
 ITEM             NUMBER(10,0), 
 CLIENTE          NUMBER(10,0), 
 TIPO_VEI         NUMBER(10,0), 
 EMPDESTINO       NUMBER(10,0), 
 DESTINO          NUMBER(10,0), 
 PONTOORIGEM      NUMBER(10,0), 
 PONTODESTINO     NUMBER(10,0), 
 CATEGORIA        NUMBER(10,0), 
 EMPROTA          NUMBER(10,0), 
 ROTA             CHAR(4), 
 DATASAIDA        NUMBER(10,0), 
 HORASAIDA        NUMBER(10,0), 
 DATACHEGADA      NUMBER(10,0), 
 HORACHEGADA      NUMBER(10,0), 
 LOCALEMBARQUE    VARCHAR2(200 BYTE), 
 LOCALDESEMBARQUE VARCHAR2(200 byte), 
 MOTORISTA1       NUMBER(10,0), 
 MOTORISTA2       NUMBER(10,0), 
 VENDEDOR         NUMBER(10,0), 
 VALOR            NUMBER 
)*/ 
 
TYPE tLIST IS REF CURSOR; 
SLIST TLIST; 
vLIST T_SERVICO_FRETAMENTO_VIAGENS := T_SERVICO_FRETAMENTO_VIAGENS(0,0,null,0,0,0,0,0,0,0,0,0,null,0,0,0,0,null,null,0,0,0,0); 
xLIST  T_SERVICO_FRETA_VIAGENS_TABELA := T_SERVICO_FRETA_VIAGENS_TABELA(); 
wVET NUMBER := 0; 
BEGIN 
  OPEN SLIST FOR 
    SELECT SFR.EMPRESA, SFR.CODIGO, SFR.TIPO, SFR.CLIENTE, SFR.EMPDESTINO, SFR.DESTINO, ISEF.ITEM, ISEF.TIPO_VEI, ISEF.EMPROTA, ISEF.ROTA, ISEF.CATEGORIASERVICO, 
           ISEF.PNTREFORIGEM, ISEF.PNTREFDESTINO, ISEF.LOCALEMBARQUE, ISEF.LOCALDESEMBARQUE, ISEF.HORARIO, ISEF.HORARIOCHEGADA, 
           ISEF.DATASERVICO,ISEF.DATASERVICO + ISEF.QUANTIDADEDIAS -1, ISEF.MOTORISTA1, ISEF.MOTORISTA2, SFR.VENDEDOR, ISEF.VALOR 
    FROM ITENSSERVICOFRETAMENTO ISEF 
    INNER JOIN SERVICOSFRETAMENTO SFR ON SFR.EMPRESA = ISEF.EMPRESA AND SFR.CODIGO = ISEF.SERVICO 
    WHERE SFR.TIPO IN ('Eventual','Receptivo') AND SFR.EMPRESA = pEMPRESA AND ISEF.DATASERVICO BETWEEN pDATAINICIAL AND pDATAFINAL; 
  LOOP 
    FETCH sLIST INTO vLIST.EMPRESA, vLIST.SERVICO, vLIST.TIPO, vLIST.CLIENTE, vLIST.EMPDESTINO, vLIST.DESTINO, vLIST.ITEM, 
                     vLIST.TIPO_VEI, vLIST.EMPROTA, vLIST.ROTA, vLIST.CATEGORIA, vLIST.PONTOORIGEM, vLIST.PONTODESTINO, 
                     VLIST.LOCALEMBARQUE, VLIST.LOCALDESEMBARQUE, VLIST.HORASAIDA, VLIST.HORACHEGADA, VLIST.DATASAIDA, VLIST.DATACHEGADA, 
                     vLIST.MOTORISTA1, vLIST.MOTORISTA2, vLIST.VENDEDOR, vLIST.VALOR; 
    EXIT WHEN sLIST%NOTFOUND; 
 
    wVET := wVET + 1; 
    xLIST.extend; 
    xLIST(wVET) := vLIST; 
  END LOOP; 
  CLOSE SLIST; 
 
  FOR wDATA IN PDATAINICIAL..PDATAFINAL 
  LOOP 
    OPEN sLIST FOR 
      SELECT SFR.EMPRESA, SFR.CODIGO, SFR.TIPO, SFR.CLIENTE, SFR.EMPDESTINO, SFR.DESTINO, ISEF.ITEM, ISEF.TIPO_VEI, ISEF.EMPROTA, ISEF.ROTA, ISEF.CATEGORIASERVICO, 
             ISEF.PNTREFORIGEM, ISEF.PNTREFDESTINO, ISEF.LOCALEMBARQUE, ISEF.LOCALDESEMBARQUE, ISEF.HORARIO, ISEF.HORARIOCHEGADA, 
             ISEF.DATASERVICO,ISEF.DATASERVICO + ISEF.QUANTIDADEDIAS -1, ISEF.MOTORISTA1, ISEF.MOTORISTA2, SFR.VENDEDOR, ISEF.VALOR 
      FROM ITENSSERVICOFRETAMENTO ISEF 
      INNER JOIN SERVICOSFRETAMENTO SFR ON SFR.EMPRESA = ISEF.EMPRESA AND SFR.CODIGO = ISEF.SERVICO 
      WHERE SFR.TIPO = 'Continuo' AND SFR.EMPRESA = pEMPRESA AND 
            FUNCAO_VALIDAVIAGEMFRETAMENTO(SFR.EMPRESA, SFR.CODIGO, ISEF.ITEM, wDATA, 'N',pRETCANC) > 0; 
    LOOP 
      FETCH sLIST INTO vLIST.EMPRESA, vLIST.SERVICO, vLIST.TIPO, vLIST.CLIENTE, vLIST.EMPDESTINO, vLIST.DESTINO, vLIST.ITEM, 
                       vLIST.TIPO_VEI, vLIST.EMPROTA, vLIST.ROTA, vLIST.CATEGORIA, vLIST.PONTOORIGEM, vLIST.PONTODESTINO, 
                       VLIST.LOCALEMBARQUE, VLIST.LOCALDESEMBARQUE, VLIST.HORASAIDA, VLIST.HORACHEGADA, VLIST.DATASAIDA, VLIST.DATACHEGADA, 
                       vLIST.MOTORISTA1, vLIST.MOTORISTA2, vLIST.VENDEDOR, vLIST.VALOR; 
      EXIT WHEN SLIST%NOTFOUND; 
      vLIST.DATASAIDA := WDATA; 
      vLIST.DATACHEGADA  := wDATA; 
      wVET := wVET + 1; 
      xLIST.extend; 
      xLIST(wVET) := vLIST; 
    END LOOP; 
    CLOSE SLIST; 
    IF pRETPERIODO = 'S' THEN 
      OPEN sLIST FOR 
        SELECT SFR.EMPRESA, SFR.CODIGO, SFR.TIPO, SFR.CLIENTE, SFR.EMPDESTINO, SFR.DESTINO, ISEF.ITEM, ISEF.TIPO_VEI, ISEF.EMPROTA, ISEF.ROTA, ISEF.CATEGORIASERVICO, 
               ISEF.PNTREFORIGEM, ISEF.PNTREFDESTINO, ISEF.LOCALEMBARQUE, ISEF.LOCALDESEMBARQUE, ISEF.HORARIO, ISEF.HORARIOCHEGADA, 
               ISEF.DATASERVICO,SFR.DATACHEGADA, ISEF.MOTORISTA1, ISEF.MOTORISTA2, SFR.VENDEDOR, ISEF.VALOR 
        FROM ITENSSERVICOFRETAMENTO ISEF 
        INNER JOIN SERVICOSFRETAMENTO SFR ON SFR.EMPRESA = ISEF.EMPRESA AND SFR.CODIGO = ISEF.SERVICO 
        WHERE SFR.TIPO = 'Eventual' AND SFR.EMPRESA = pEMPRESA AND ISEF.DATASERVICO < pDATAINICIAL AND SFR.DATACHEGADA >= pDATAINICIAL AND 
              FUNCAO_VALIDAVIAGEMFRETAMENTO(SFR.EMPRESA, SFR.CODIGO, ISEF.ITEM, wDATA, 'S',pRETCANC) > 0; 
      LOOP 
        FETCH sLIST INTO vLIST.EMPRESA, vLIST.SERVICO, vLIST.TIPO, vLIST.CLIENTE, vLIST.EMPDESTINO, vLIST.DESTINO, vLIST.ITEM, 
                         vLIST.TIPO_VEI, vLIST.EMPROTA, vLIST.ROTA, vLIST.CATEGORIA, vLIST.PONTOORIGEM, vLIST.PONTODESTINO, 
                         VLIST.LOCALEMBARQUE, VLIST.LOCALDESEMBARQUE, VLIST.HORASAIDA, VLIST.HORACHEGADA, VLIST.DATASAIDA, VLIST.DATACHEGADA, 
                         vLIST.MOTORISTA1, vLIST.MOTORISTA2, vLIST.VENDEDOR, vLIST.VALOR; 
        EXIT WHEN SLIST%NOTFOUND; 
        wVET := wVET + 1; 
        xLIST.extend; 
        xLIST(wVET) := vLIST; 
      END LOOP; 
      CLOSE SLIST; 
 
    END IF; 
  END LOOP; 
  RETURN(xLIST); 
END FUNCAO_BUSCA_FRETA_VIAGENS;


Solution

  • thought and analyzing the situation, the result of these three sentences are being added in a return vector. So, I made this union all to be returned as a result of a query. Am I on the right path?

    create or replace
    FUNCTION softbus.funcao_busca_freta_viagens 
    (pEMPRESA IN NUMERIC, pDATAINICIAL IN NUMERIC, pDATAFINAL IN NUMERIC, pRETPERIODO IN CHAR, pRETCANC IN CHAR) 
    RETURNS table(  
     EMPRESA          NUMERIC(10,0), 
     SERVICO          NUMERIC(10,0), 
     TIPO             CHAR(10), 
     ITEM             NUMERIC(10,0), 
     CLIENTE          NUMERIC(10,0), 
     TIPO_VEI         NUMERIC(10,0), 
     EMPDESTINO       NUMERIC(10,0), 
     DESTINO          NUMERIC(10,0), 
     PONTOORIGEM      NUMERIC(10,0), 
     PONTODESTINO     NUMERIC(10,0), 
     CATEGORIA        NUMERIC(10,0), 
     EMPROTA          NUMERIC(10,0), 
     ROTA             CHAR(4), 
     DATASAIDA        NUMERIC(10,0), 
     HORASAIDA        NUMERIC(10,0), 
     DATACHEGADA      NUMERIC(10,0), 
     HORACHEGADA      NUMERIC(10,0), 
     LOCALEMBARQUE    VARCHAR(200), 
     LOCALDESEMBARQUE VARCHAR(200), 
     MOTORISTA1       NUMERIC(10,0), 
     MOTORISTA2       NUMERIC(10,0), 
     VENDEDOR         NUMERIC(10,0), 
     VALOR            NUMERIC 
    ) 
    AS $body$
    DECLARE 
    BEGIN
        RETURN QUERY
        SELECT SFR.EMPRESA, SFR.CODIGO, SFR.TIPO, SFR.CLIENTE, SFR.EMPDESTINO, SFR.DESTINO, ISEF.ITEM, ISEF.TIPO_VEI, ISEF.EMPROTA, ISEF.ROTA, ISEF.CATEGORIASERVICO, 
               ISEF.PNTREFORIGEM, ISEF.PNTREFDESTINO, ISEF.LOCALEMBARQUE, ISEF.LOCALDESEMBARQUE, ISEF.HORARIO, ISEF.HORARIOCHEGADA, 
               ISEF.DATASERVICO,ISEF.DATASERVICO + ISEF.QUANTIDADEDIAS -1, ISEF.MOTORISTA1, ISEF.MOTORISTA2, SFR.VENDEDOR, ISEF.VALOR 
        FROM ITENSSERVICOFRETAMENTO ISEF 
        INNER JOIN SERVICOSFRETAMENTO SFR ON SFR.EMPRESA = ISEF.EMPRESA AND SFR.CODIGO = ISEF.SERVICO 
        WHERE SFR.TIPO IN ('Eventual','Receptivo') AND SFR.EMPRESA = pEMPRESA AND ISEF.DATASERVICO BETWEEN pDATAINICIAL AND pDATAFINAL
        UNION ALL
        SELECT SFR.EMPRESA, SFR.CODIGO, SFR.TIPO, SFR.CLIENTE, SFR.EMPDESTINO, SFR.DESTINO, ISEF.ITEM, ISEF.TIPO_VEI, ISEF.EMPROTA, ISEF.ROTA, ISEF.CATEGORIASERVICO, 
                 ISEF.PNTREFORIGEM, ISEF.PNTREFDESTINO, ISEF.LOCALEMBARQUE, ISEF.LOCALDESEMBARQUE, ISEF.HORARIO, ISEF.HORARIOCHEGADA, 
                 ISEF.DATASERVICO,ISEF.DATASERVICO + ISEF.QUANTIDADEDIAS -1, ISEF.MOTORISTA1, ISEF.MOTORISTA2, SFR.VENDEDOR, ISEF.VALOR 
          FROM ITENSSERVICOFRETAMENTO ISEF 
          INNER JOIN SERVICOSFRETAMENTO SFR ON SFR.EMPRESA = ISEF.EMPRESA AND SFR.CODIGO = ISEF.SERVICO 
          WHERE SFR.TIPO = 'Continuo' AND SFR.EMPRESA = pEMPRESA AND ISEF.DATASERVICO BETWEEN pDATAINICIAL AND pDATAFINAL AND
                FUNCAO_VALIDAVIAGEMFRETAMENTO(SFR.EMPRESA, SFR.CODIGO, ISEF.ITEM, ISEF.DATASERVICO, 'N',pRETCANC) > 0 
        UNION ALL 
        SELECT SFR.EMPRESA, SFR.CODIGO, SFR.TIPO, SFR.CLIENTE, SFR.EMPDESTINO, SFR.DESTINO, ISEF.ITEM, ISEF.TIPO_VEI, ISEF.EMPROTA, ISEF.ROTA, ISEF.CATEGORIASERVICO, 
                   ISEF.PNTREFORIGEM, ISEF.PNTREFDESTINO, ISEF.LOCALEMBARQUE, ISEF.LOCALDESEMBARQUE, ISEF.HORARIO, ISEF.HORARIOCHEGADA, 
                   ISEF.DATASERVICO,SFR.DATACHEGADA, ISEF.MOTORISTA1, ISEF.MOTORISTA2, SFR.VENDEDOR, ISEF.VALOR 
            FROM ITENSSERVICOFRETAMENTO ISEF 
            INNER JOIN SERVICOSFRETAMENTO SFR ON SFR.EMPRESA = ISEF.EMPRESA AND SFR.CODIGO = ISEF.SERVICO 
            WHERE SFR.TIPO = 'Eventual' AND SFR.EMPRESA = pEMPRESA AND ISEF.DATASERVICO < pDATAINICIAL AND SFR.DATACHEGADA >= pDATAINICIAL AND 
                  FUNCAO_VALIDAVIAGEMFRETAMENTO(SFR.EMPRESA, SFR.CODIGO, ISEF.ITEM, ISEF.DATASERVICO, 'S',pRETCANC) > 0; 
            
    END;
    $body$
    LANGUAGE PLPGSQL
    SECURITY DEFINER
    STABLE;