org.postgresql.util.PSQLException: ERROR: column "row_count" does not exist

Question

I tried to execute a Trigger function which checks the value of ROW_COUNT and performs the necessary operation. The trigger function is being called by a trigger. The trigger function definition is:

create function MB_MDDeleteDefinitionPGSQL() returns trigger language plpgsql as $$ 
declare integer_var bigint ; 
begin 
GET DIAGNOSTICS integer_var = ROW_COUNT; 
if ROW_COUNT > 0 then 
delete from MB_MDFieldProps where propId = OLD.commonPropsId; 
delete from MB_MDCustomFieldProps where customId = old.customId and old.reusableId is null; 
end if; 
end $$;

And the trigger which calls the above function is

create trigger MB_MDDeleteDefinition before delete on MB_MDDefinition for each row 
execute procedure MB_MDDeleteDefinitionPGSQL();

score 2 · Answer 1 · answered Nov 27 '20 at 09:54

You just don't need that ROW_COUNT logic. The trigger fires for each row that is deleted; if no row is deleted, then it does not fire at all.

Note, however, that it would be much simpler (and more efficient) to set foreign key constraints on the dependent tables, which would simply avoid the need for a trigger.

For example:

create table mb_mdfieldprops (
    ...
    propId int 
        references mb_mddefinition(commonPropsId)
        on delete cascade
);

org.postgresql.util.PSQLException: ERROR: column "row_count" does not exist

1 Answers1