CODEWITHSUNDEEP
postgresqldatetriggerssyntax-error
3va
3va

Reputation: 85

PostgreSQL date trigger syntax error

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?

Upvotes: 1

Views: 128

Answers (1)

klin
klin

Reputation: 121754

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
$$;

Upvotes: 1

Related Questions