Trying to create Postgres function

Question

In the create screen of the Postgress GUI I'm getting a syntax error at the while statement. Language is plpgsql and return type is void. TIA

enter code here

begin  
declare counter integer := 1;  
declare CurrentDate Date := '1/1/2018';  
    while CurrentDate < '1/1/2019'  
    loop  
        insert into dimCalendar select CurrentDate, EXTRACT(DOW FROM  
current_date), EXTRACT(DOY FROM  current_date);  
        CurrentDate := CurrentDate + 1;  
    end loop  
end

Show us the complete code. But declare needs to go before the begin, you only need one declare and '1/1/2018' is a string literal not a date. But you don't need a function for that to begin with. — a_horse_with_no_name
Hey thanks, I was able to get that to compile/save by moving the Declare, as you said. '1/1/2018' should act as a date since the variable is declared Date. Just like select cast('12/31/2018' as date) + 1 yields 2019-01-01. Yes, I suppose I don't need a function for this but trying to write it inline is even a bigger hassle. — KTrock

Neil McGuigan Neil McGuigan · Accepted Answer · 2018-03-08T00:46:22

Next time, try to include all the code please

Always use ISO date format, unless you have a reason not to

declare goes before begin

You're missing a semi-colon after "end loop"

Use snake_case in PostgreSQL please

create or replace function foo() returns void as $$
declare
    counter integer := 1;
    curr_date date := '2018-01-01';
begin

    while curr_date < '2019-01-01' loop

        raise notice 'curr_date: %', curr_date;

        curr_date := curr_date + 1;

    end loop;

end
$$ language plpgsql;

Trying to create Postgres function

1 Answers