PostgreSQL date trigger syntax error

Question

I'm a newbie at SQL and I have no idea what I am doing wrong trying to execute that code in PostgreSQL (I added numbers here to mark the lines):

create function annouc_dates()
returns trigger as '
begin
    if new.creation_date is NULL then
        new.creation_date := select current_timestamp;
    end if;
    if new.removal_date is NULL then
        new.removal_date := select new.creation_date + interval '2 week';
    end if;
    return new;
end';

I get a message: ERROR: syntax error at or near "2" LINE 8: ...emoval_date := new.creation_date + interval '2 week';

can anyone help me?

klin · Accepted Answer · 2018-04-12 11:10:49Z

1

The issue concerns nested quoting. Use Postgres dollar-quoting as described in 4.1.2.4. Dollar-quoted String Constants.

create function annouc_dates()
returns trigger language plpgsql as $$
begin
    if new.creation_date is NULL then
        new.creation_date := current_timestamp;
    end if;
    if new.removal_date is NULL then
        new.removal_date := new.creation_date + interval '2 week';
    end if;
    return new;
end
$$;

edited Apr 12, 2018 at 11:10

answered Apr 12, 2018 at 10:49

klin

123k15 gold badges241 silver badges263 bronze badges

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

3 Comments

3va Over a year ago

I did as you adviced me, but I got error that the language is not specified, so I added clause 'language plpgsql' and then I got error that still something is wrong with line 5. 'ERROR: syntax error at or near "select" LINE 5: new.creation_date := select current_timestamp;'

user330315 Over a year ago

new.creation_date := select current_timestamp; can be simplified to new.creation_date := current_timestamp; and the same for the assignment of new.removal_date

klin Over a year ago

I focused on the error message and missed other blunders, sorry. See the updated answer.

Collectives™ on Stack Overflow

PostgreSQL date trigger syntax error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related