Postgresql trigger syntax error at or near "NEW"

Question

Here is what i'm trying to do:

ALTER TABLE publishroomcontacts ADD COLUMN IF NOT EXISTS contactorder integer NOT NULL default 1;

CREATE OR REPLACE FUNCTION publishroomcontactorder() RETURNS trigger AS $publishroomcontacts$
    BEGIN   
      IF (TG_OP = 'INSERT') THEN
            with newcontactorder as (SELECT contactorder FROM publishroomcontacts WHERE publishroomid = NEW.publishroomid ORDER BY contactorder limit 1)    
            NEW.contactorder = (newcontactorder + 1);
        END IF;
        RETURN NEW;
    END;
$publishroomcontacts$ LANGUAGE plpgsql;

CREATE TRIGGER publishroomcontacts BEFORE INSERT OR UPDATE ON publishroomcontacts
    FOR EACH ROW EXECUTE PROCEDURE publishroomcontactorder();

I've been looking into a lot of examples and they all look like this. Most of them a couple of years old tho. Has this changed or why doesn't NEW work? And do i have to do the insert in the function or does postgres do the insert with the returned NEW object after the function is done?

that approach is not going to work in a multiuser environment. — Jasen
– Jasen, Commented Aug 14, 2018 at 9:33
@Jasen is there a better approach for multiuser environment? :) — Daniel Gustafsson
– Daniel Gustafsson, Commented Aug 14, 2018 at 9:36

Kamil Gosciminski · Accepted Answer · 2018-08-14 08:42:38Z

I'm not sure what you're trying to do, but your syntax is wrong here:

with newcontactorder as (SELECT contactorder FROM publishroomcontacts WHERE publishroomid = NEW.publishroomid ORDER BY contactorder limit 1)    
NEW.contactorder = (newcontactorder + 1);

Do not use CTE query if there is no select that comes afterwards. If you want to increment contactorder column for particular publishroomid whenever new one is being added and this is your sequence (auto increment) mechanism then you should replace it with:

NEW.contactorder = COALESCE((
    SELECT max(contactorder)
    FROM publishroomcontacts
    WHERE publishroomid = NEW.publishroomid
    ), 1);

Note the changes:

there's no CTE, just variable assignment with SELECT query
use MAX() aggregate function instead of ORDER BY + LIMIT
wrapped up with COALESCE(x,1) function to properly insert first contacts for rooms, it will return 1 if your query does return NULL

Your trigger should look like this

CREATE OR REPLACE FUNCTION publishroomcontactorder() RETURNS trigger AS $publishroomcontacts$
BEGIN   
  IF (TG_OP = 'INSERT') THEN
    NEW.contactorder = COALESCE((
      SELECT max(contactorder) + 1
      FROM publishroomcontacts
      WHERE publishroomid = NEW.publishroomid
      ), 1);
  END IF;
  RETURN NEW;
END;
$publishroomcontacts$ LANGUAGE plpgsql;

Postgres will insert the row itself, you don't have to do anything, because RETURN NEW does that.

This solution does not take care of concurrent inserts which makes it unsafe for multi-user environment! You can work around this by performing an UPSERT !

Laurenz Albe · Accepted Answer · 2018-08-14 08:38:28Z

1

WITH is not an assignment in PL/pgSQL.

PL/pgSQL interprets the line as SQL statement, but that is bad SQL because the WITH clause is followed by NEW.contactorder rather than SELECT or another CTE.

Hence the error; it has nothing to do with NEW as such.

You probably want something like

SELECT contactorder INTO newcontactorder
FROM publishroomcontacts
WHERE publishroomid = NEW.publishroomid
ORDER BY contactorder DESC  -- you want the biggest one, right?
LIMIT 1;

You'll have to declare newcontactorder in the DECLARE section.

Warning: If there are two concurrent inserts, they might end up with the same newcontactorder.

answered Aug 14, 2018 at 8:38

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

1 Comment

Daniel Gustafsson Over a year ago

Good answer! Thx! :)

Collectives™ on Stack Overflow

Postgresql trigger syntax error at or near "NEW"

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related