CODEWITHSUNDEEP
postgresqlplpgsqlpsqlpostgresql-9.5
Alex Totheroh
Alex Totheroh

Reputation: 167

Postgres plpgsql error when trying to create function

Postgres 9.5.2

Getting: 

ERROR:  syntax error at end of input
LINE 14: ...ast_modified_at, NEW.first_name, NEW.last_name);

for these statements in a psql shell, right after running $$;:

CREATE OR REPLACE FUNCTION customer_history_trigger() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
BEGIN
    NEW.last_modified_at = now();
    IF (TG_OP = 'UPDATE') THEN
        IF OLD.status IS DISTINCT FROM NEW.status or OLD.parent_id IS DISTINCT FROM NEW.parent_id
        or OLD.first_name IS DISTINCT FROM NEW.first_name or OLD.last_name IS DISTINCT FROM NEW.last_name
        or OLD.company IS DISTINCT FROM NEW.company or OLD.company_alias IS DISTINCT FROM NEW.company_alias
        THEN
            INSERT INTO customerhistory
                (customer_id, modified_at, first_name, last_name)
            VALUES
                (NEW.id, NEW.last_modified_at, NEW.first_name, NEW.last_name);
        END IF;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO customerhistory
            (customer_id, modified_at, first_name, last_name)
        VALUES
            (NEW.id, NEW.last_modified_at, NEW.first_name, NEW.last_name);
    END IF;
    RETURN NEW;
END;
$$;

There's also a little arrow in the error pointing to the semicolon at the end of the line. So clearly, it doesn't like the semi colon at the end of this line: (NEW.id, NEW.last_modified_at, NEW.first_name, NEW.last_name); but I can't figure out why.

Upvotes: 0

Views: 64

Answers (1)

Alex Totheroh
Alex Totheroh

Reputation: 167

Turns out this is an issue having to do with the line breaks after INSERT INTO customerhistory and VALUES. If I remove those two line breaks, and the other two further down, then paste the statement into psql, it works. It does not work with those line breaks, however.

Perhaps it would also work if I opened the parenthesis on the same line? Didn't test that.

Upvotes: 1

Related Questions