CODEWITHSUNDEEP
postgresql
Daniel Gustafsson
Daniel Gustafsson

Reputation: 1827

Postgresql trigger syntax error at or near "NEW"

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?

Upvotes: 2

Views: 2720

Answers (2)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

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 !

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 248305

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.

Upvotes: 1

Related Questions