CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.5
Sohum Prabhudesai
Sohum Prabhudesai

Reputation: 23

ERROR: column "new" of relation xyz does not exist in postgresql Trigger

I am trying to generate a code for column tpl_league_code using a trigger after insert into the tpl_league_tbl table. New entry is inserted first and then the update should occur but I get an error saying column new of relation tpl_league_tbl doesn't exist.

Here is my function script 

CREATE OR REPLACE FUNCTION createLeagueCode()
RETURNS trigger AS
$BODY$
DECLARE
  leagueCode character varying(25);
BEGIN
  leagueCode := 'LEAUGECODE'||(SELECT COUNT(*) FROM tpl_league_tbl)||
(SELECT CAST (NOW() AS CHARACTER VARYING(10)));
  UPDATE tpl_league_tbl SET new.tpl_league_code=leagueCode;
  RETURN new;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

This is my trigger 

CREATE TRIGGER createLeagueTrigger
AFTER INSERT
ON tpl_league_tbl
FOR EACH ROW
EXECUTE PROCEDURE createLeagueCode();

Upvotes: 0

Views: 1499

Answers (2)

Abbas Mashayekh
Abbas Mashayekh

Reputation: 301

You don't need a trigger for such a simple task. It's overkill. Just create a sequence and use it instead of using count. Something like this:

CREATE SEQUENCE league_code START 1;

INSERT INTO tpl_league_tbl (..., leagueCode)
VALUES (..., 'LEAUGECODE' || nextval('league_code') || now()::varchar(10))

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246878

The UPDATE statement cannot work because table tpl_league_tbl does not have a column new. You would have to omit the new..

But you should not do this in anAFTER trigger, you should do it in a BEFORE trigger like this:

NEW.tpl_league_code := leagueCode;

Then when you RETURN NEW;, the new row has been modified before it is inserted, which is what you want. It is cumbersome and expensive to insert a row only to update it a split second later.

Another thing: You should not run

SELECT count(*) FROM tpl_league_tbl;

inside the trigger function, because this is a very expensive operation that requires a sequential table scan.

If you need a random, unique suffix for leagueCode, I recommend that you use a sequence and get the number with nextval. That will be much cheaper.

Upvotes: 1

Related Questions