Date no postgresql.PL/SQL

Asked

Viewed 60 times

0

with the function below I want to make this function compare the current date with the date stored in the bank.HOWEVER ,THERE IS NO RETURN .

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';
sql postgresql

  • What error is presented to you?

  • ACTUALLY IT IS NOT A MISTAKE. THE RETURN OF THE FUNCTION IS NOT RETURNING ANYTHING WHEN IN FACT IT WAS TO RETURN . THE PROBLEM IS THE COMPARISON THAT I MAKE FROM DATE = DAY THAT IS NOT BEING MADE . I DO NOT KNOW HOW TO FIX.

  • and its market table, has data for the current day?

  • no !!!! i also want to compare the current date that I get with this SELECT function CURRENT_DATE with this value returned from the database '7-02-18' however, the comparison is not being made.

1 answer

1


It returns nothing because the date in the table is 18-02 and today is 17-02. Anyway it is much 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 overturn (drop) its function before creating this new because it changes the type of return.

  • Truth bro !! My distraction ! I’ll use that shape you made! Thanks

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.