CODEWITHSUNDEEP
sqlpostgresqlplpgsqlpostgresql-9.3
Harnish Kumar
Harnish Kumar

Reputation: 355

How to get the row count of update,insert,delete query in postgresql

I have a postgresql tables. user may update records or delete records manually using interface or using query. I need to track how many records have been updated ,deleted into a log table.I had tried as below update operations count can be tracked successfully. But delete operations count are not logging to log table.

CREATE OR REPLACE FUNCTION edmonton.count_func() RETURNS TRIGGER AS $body$
DECLARE
v_old_data TEXT;
v_new_data TEXT;
log_count INTEGER;
BEGIN


IF (TG_OP = 'UPDATE') THEN
v_old_data := ROW(OLD.*);
v_new_data := ROW(NEW.*);
log_count = count(row(new));
RAISE NOTICE 'update operations';
INSERT INTO edmonton.log ("timestamp",message,type,project_area,count)
VALUES (CURRENT_TIMESTAMP,'records updated in Builder 
table','Information','edmonton',log_count);
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
v_old_data := ROW(OLD.*);
log_count = count(row(old));
RAISE NOTICE 'delete operations';
INSERT INTO edmonton.log ("timestamp",message,type,project_area,count)
VALUES (CURRENT_TIMESTAMP,'records deleted in Builder 
table','Information','edmonton',log_count);
RETURN OLD;
ELSIF (TG_OP = 'INSERT') THEN
v_new_data := ROW(NEW.*);
log_count = count(row(new));
RAISE NOTICE 'insert operations';
INSERT INTO edmonton.log ("timestamp",message,type,project_area,count)
VALUES (CURRENT_TIMESTAMP,'records inserted in Builder 
table','Information','edmonton',log_count);
RETURN NEW;
ELSE
RAISE WARNING '[EDMONTON.COUNT_FUNC] - Other action occurred: %, at 
%',TG_OP,now();
RETURN NULL;
END IF;

EXCEPTION
WHEN data_exception THEN
RAISE WARNING '[EDMONTON.COUNT_FUNC] - UDF ERROR [DATA EXCEPTION] - 
SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN unique_violation THEN
RAISE WARNING '[EDMONTON.COUNT_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %, 
SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN OTHERS THEN
RAISE WARNING '[EDMONTON.COUNT_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %, 
SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
END;
$body$
LANGUAGE plpgsql
 SECURITY DEFINER
SET search_path = pg_catalog, edmonton;

And this is my trigger

CREATE TRIGGER count_audit
AFTER INSERT OR UPDATE OR DELETE
ON edmonton.builder
FOR EACH STATEMENT
EXECUTE PROCEDURE edmonton.count_func();

I am getting a warning message when updating a record and messages were not logged to log table.

WARNING:  [EDMONTON.COUNT_FUNC] - UDF ERROR [OTHER] - SQLSTATE: 55000, SQLERRM: record "old" is not assigned yet
CONTEXT:  PL/pgSQL function count_func() line 49 at RAISE

Upvotes: 1

Views: 4805

Answers (2)

klin
klin

Reputation: 121594

First, you are not able to count inserted/updated/deleted rows that way. The expression 

count(row(new))

always returns 1 as row(new) is a single row.

Second, the records new and old are not set in a trigger for each statement. Per the documentation:

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is unassigned in statement-level triggers and for DELETE operations.

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51456

https://www.postgresql.org/docs/current/static/plpgsql-trigger.html

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is unassigned in statement-level triggers and for DELETE operations.

OLD

Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is unassigned in statement-level triggers and for INSERT operations.

So the real question is

how does update/insert work?..

Upvotes: 1

Related Questions