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. — user330315
– user330315, Commented Mar 7, 2018 at 21:34
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
– KTrock, Commented Mar 7, 2018 at 21:53

Neil McGuigan · Accepted Answer · 2022-12-14 06:16:42Z

2

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;

edited Dec 14, 2022 at 6:16

answered Mar 8, 2018 at 0:46

Neil McGuigan

48.5k12 gold badges130 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trying to create Postgres function

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related