CODEWITHSUNDEEP
sqlpostgresqltriggersplpgsqlinsert-update
sharafjaffri
sharafjaffri

Reputation: 2214

Execute Update Always SET Found to FALSE

I have wrote following trigger.

CREATE OR REPLACE FUNCTION tbl_scdr_insert_trigger() RETURNS TRIGGER AS $$
<< fk >>
DECLARE
    rowcount int;
    myrec RECORD;
BEGIN
    EXECUTE 'UPDATE sc_'|| to_char(NEW.start_datetime, 'YYYY_MM') ||' SET a = a + ($1).a, b = b + ($1).b WHERE (e_id = ($1).e_id AND start_datetime = ($1).start_datetime AND c_id = ($1).c_id'
    USING NEW;
    GET DIAGNOSTICS rowcount = ROW_COUNT;
    RAISE NOTICE 'found %', rowcount;
    IF not found THEN
        RAISE NOTICE 'not found';
    ELSIF found THEN
        RAISE NOTICE 'found';
        RETURN NULL;
    END IF;
END;
$$
LANGUAGE plpgsql;

When Ever Called this always return Zero as Rowcount because statement is not committed Yet and Found is always False even there are records with same values of where clause columns.

Upvotes: 0

Views: 239

Answers (1)

Kouber Saparev
Kouber Saparev

Reputation: 8115

As the PostgreSQL documentation states:

EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND

That means that FOUND will always be FALSE in your case, because you are using EXECUTE and not a static UPDATE statement.

Upvotes: 2

Related Questions