Before insert trigger to replace new data postgres

Question

I have a table with two data columns: col1 and col2. Col1 is text field and col2 is time. Col1 is required, col2 is not, so it should have a default value of null. I use pgAdmin, which is completely new to me, as sql trigger is. I have the following trigger function code:

CREATE OR REPLACE FUNCTION schema.table_replace()
  RETURNS trigger AS
  LANGUAGE 'plpgsql';
$BODY$
BEGIN
    (CASE
        WHEN NEW.col1='111' THEN NEW.col1='aaa'
        WHEN NEW.col1='222' THEN NEW.col1='bbb'
        WHEN NEW.col1='333' THEN NEW.col1='ccc'
        ELSE NEW.col1='error'
    END);
return NEW;
END;
$BODY$

And that could be the before trigger (just current values should be affected, not all rows):

CREATE TRIGGER schema.table_replace
  BEFORE INSERT
  ON schema.table
  EXECUTE PROCEDURE schema.table_replace();

To tell the truth, I know nothing about pgAdmin, it seems to be much more complicated than writing the code and run it with query tool. The problem is to handle the case when there is no second value (it is optional), and in this case the col2 of the row should be left untouched, and the SQL code returns errors as well. Could you give some help to make it running and creating the function and the trigger? Thanks.

Barfuss

Answer 1

CASE as a control structure is closed with END CASE (whereas the expression is closed with just an END). And in the branches there are statements, they need to be terminated by a semicolon.

Your LANGUAGE is also misplaced. That belongs at the end. And you don't need the single quotes.

You can use an IF to only do the replacement, when col2 is not null.

CREATE OR REPLACE FUNCTION schema.table_replace()
                           RETURNS trigger AS
$BODY$
BEGIN
  IF NEW.col2 IS NOT NULL THEN
    CASE
      WHEN NEW.col1 = '111' THEN
        NEW.col1 = 'aaa';
      WHEN NEW.col1 = '222' THEN
        NEW.col1 = 'bbb';
      WHEN NEW.col1 = '333' THEN
        NEW.col1 = 'ccc';
      ELSE
        NEW.col1 = 'error';
    END CASE;
  END IF;

  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

You also need to declare the trigger as a FOR EACH ROW trigger for NEW to work.

And a trigger name cannot be schema qualified.

CREATE TRIGGER table_replace
               BEFORE INSERT
               ON schema.table
               FOR EACH ROW
               EXECUTE PROCEDURE schema.table_replace();

Doggery

It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea? – Barfuss

Answer 2

The main problem of your example is missing FOR EACH ROW clause in your CREATE TRIGGER statement. Without this clause, the created trigger is a statement trigger with different behave. Your task can be solved with SQL functional CASE statement.

CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
  IF NEW.col2 NOT NULL THEN
    NEW.col1 := CASE NEW.col1
                     WHEN '111' THEN 'aaa'
                     WHEN '222' THEN 'bbb'
                     WHEN '333' THEN 'ccc'
                     ELSE 'error' END CASE;
  END IF;
  RETURN NEW;
END;
$BODY$

Behlke