Date in postgresql.PL/SQL

0

With the function below I want to make this function compare the current date with the date stored in the database.

create table mercado(

    id integer,
    diaEntrega date,
    valor integer    
);

insert into mercado values (2,'7-02-18',12); 

CREATE OR REPLACE FUNCTION get_d () RETURNS date AS    
$$    
DECLARE    
        dataAtual date;
        varValor integer;
        dataFinalizada date;                       
BEGIN    
     SELECT CURRENT_DATE INTO dataAtual;            
     /*SELECT diaEntrega FROM mercado WHERE '7-02-18' = diaEntrega INTO  dataFinalizada;*/ /*OBS:DESTA FORMA FUNCIONA*/          

     SELECT diaEntrega FROM mercado WHERE dataAtual = diaEntrega INTO dataFinalizada;

     return  dataFinalizada;        
END;
$$ LANGUAGE 'plpgsql';
    
asked by anonymous 17.02.2017 / 17:59

1 answer

1

Nothing returns because the date in the table is 18-02 and today is 17-02. Anyway, this is a lot simpler:

create or replace function get_d ()
returns setof date as $$
    select diaEntrega
    from mercado
    where current_date = diaEntrega
    ;
$$ language sql;

See that you have to return setof because there may be more than one date. You will need to drop ( drop ) your function before creating this new one because it changes the return type.

    
17.02.2017 / 20:04