How to return a new value in a trigger from a cte in PostgreSQL

Question

From my previous question in PostgreSQL (9.3), How to check a sequence efficiently for used and unused values in PostgreSQL, I now have a table chart_gap that has a list of all the unused chart numbers from table charts:

CREATE TABLE chart_gap (chart_number integer);

In trying to understand triggers a bit more, I have added the following trigger procedure taken more or less from that question:

CREATE OR REPLACE FUNCTION next_chart() RETURNS trigger AS $BODY$
BEGIN
  -- Check for empty chart number
  IF NEW.chart_number IS NULL THEN

    WITH ins AS (
      SELECT chart_number
      FROM   chart_gap
      WHERE  pg_try_advisory_xact_lock(chart_number)
      LIMIT  1
    )
    DELETE FROM chart_gap c
    USING  ins i
    WHERE  i.chart_number = c.chart_number;

    NEW.chart_number := select chart_number from ins;   <--WRONG!       
  END IF;

  RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;

The trigger procedure is tied to the charts file with:

CREATE TRIGGER next_chart
  BEFORE INSERT
  ON charts
  FOR EACH ROW
  EXECUTE PROCEDURE next_chart();

What I am attempting to do is replace the empty chart number field in charts whenever a new chart record is added. Can this be done in a trigger? (and what is the correct syntax??)

Is it not working the way you want it to? Are you getting a syntax error? What CTE has to do with what you're trying to achieve? — Jakub Kania, Sep 21 '15 at 07:14

score 1 · Accepted Answer · answered Sep 21 '15 at 08:21

In a plpgsql function you can use query RETURNING ... INTO ... as follows:

CREATE FUNCTION next_chart() RETURNS trigger AS $BODY$
BEGIN
  -- Check for empty chart number
  IF NEW.chart_number IS NULL THEN
    WITH ins AS (
      SELECT chart_number
      FROM   chart_gap
      WHERE  pg_try_advisory_xact_lock(chart_number)
      LIMIT  1
    )
    DELETE FROM chart_gap c
    USING  ins i
    WHERE  i.chart_number = c.chart_number
    RETURNING i.chart_number INTO NEW.chart_number;
  END IF;

  RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;

How to return a new value in a trigger from a cte in PostgreSQL

1 Answers1