CODEWITHSUNDEEP
sqlpostgresqlfunctiondatabase-triggersql-function
Stephen Yorke
Stephen Yorke

Reputation: 307

Postgres function to create audit table throwing error INSERT has more expressions than target columns

I have a table containing id, status, dateadded. Id and dateadded are defaulting to integer nextval and current_timestamp respectively. I then have a trigger that calls a function on Insert, Update or Delete. In the function all it does is back up and track the changes in the status table. This method worked successfully when there was more than one field being added to the table. With the status table I am only adding the status field and letting the db take care of the other 2 fields and I receive the error. It looks as if I might need to do something different in the function? I'm adding data to these tables using python to automate, and have tried adding a record to the table manually with the same error occurring. 

Error while fetching data from PostgreSQL INSERT has more expressions than target columns
LINE 2:        (TG_OP, NEW.*)
                       ^
QUERY:  INSERT INTO api_audit.d_status_list (id, status, dateadded) VALUES
       (TG_OP, NEW.*)
CONTEXT:  PL/pgSQL function api_input.d_status_list_func() line 4 at SQL statement

Function code:

create function d_status_list_func() returns trigger
    language plpgsql
as
$$
BEGIN
IF TG_OP = 'INSERT' THEN
  INSERT INTO api_audit.d_status_list (id, status, dateadded) VALUES
       (TG_OP, NEW.*);
  RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
   INSERT INTO api_audit.d_status_list (id, status, dateadded) VALUES
        (TG_OP, NEW.*);
   RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
   INSERT INTO api_audit.d_status_list (id, status, dateadded) VALUES
        (TG_OP, OLD.*);
   RETURN OLD;
END IF;
END;
$$;

alter function d_status_list_func() owner to blahblah;

Any assistance is much appreciated.

Upvotes: 0

Views: 688

Answers (1)

GMB
GMB

Reputation: 222412

The insert fails because you are declaring three columns for insert (id, status, dateadded), but you are giving it 4 values: the operation (insert, update, delete), then the 3 original columns.

Presumably, your audit table has (or should have) a column that stores the operation that is being performed.

If so, you should list that column in the insert statement:

INSERT INTO api_audit.d_status_list (operation, id, status, dateadded) 
VALUES (TG_OP, NEW.*);

Generally speaking, it is a good practice to avoid * and explicitely list the columns, which makes things easier to track down when they go wrong, so:

INSERT INTO api_audit.d_status_list (operation, id, status, dateadded) 
VALUES (TG_OP, NEW.id, NEW.status, NEW.dateadded);

Upvotes: 2

Related Questions