freshgrill
asked on
Postgres Create Function syntax error
Recently updated databases from 8.4 to 9.0. Instead of doing full dump and restore, I am moving items over and cleaning up along the way.
We have a function called generate_recurrences (originally: http://justatheory.com/computers/databases/postgresql/recurring_events.html).
I have create the datatype recurrence, but when I try to create the function, I get the error:
ERROR: syntax error at or near "check"
LINE 37: WHILE to_char(next_date, 'DD') <> check LOOP
We have a function called generate_recurrences (originally: http://justatheory.com/computers/databases/postgresql/recurring_events.html).
I have create the datatype recurrence, but when I try to create the function, I get the error:
ERROR: syntax error at or near "check"
LINE 37: WHILE to_char(next_date, 'DD') <> check LOOP
CREATE DOMAIN recurrence AS TEXT
CHECK ( VALUE IN ( 'none', 'daily', 'weekly', 'monthly' ) );
CREATE OR REPLACE FUNCTION generate_recurrences(
recurs RECURRENCE,
start_date DATE,
end_date DATE
)
RETURNS setof DATE
LANGUAGE plpgsql IMMUTABLE
AS $BODY$
DECLARE
next_date DATE := start_date;
duration INTERVAL;
day INTERVAL;
check TEXT;
BEGIN
IF recurs = 'none' THEN
-- Only one date ever.
RETURN next next_date;
ELSIF recurs = 'weekly' THEN
duration := '7 days'::interval;
WHILE next_date <= end_date LOOP
RETURN NEXT next_date;
next_date := next_date + duration;
END LOOP;
ELSIF recurs = 'daily' THEN
duration := '1 day'::interval;
WHILE next_date <= end_date LOOP
RETURN NEXT next_date;
next_date := next_date + duration;
END LOOP;
ELSIF recurs = 'monthly' THEN
duration := '27 days'::interval;
day := '1 day'::interval;
check := to_char(start_date, 'DD');
WHILE next_date <= end_date LOOP
RETURN NEXT next_date;
next_date := next_date + duration;
WHILE to_char(next_date, 'DD') <> check LOOP
next_date := next_date + day;
END LOOP;
END LOOP;
ELSE
-- Someone needs to update this function, methinks.
RAISE EXCEPTION 'Recurrence % not supported by generate_recurrences()', recurs;
END IF;
END;
$BODY$;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER