In one of negative test row-level trigger results into loop

[Amit Kapila <amit.kapila@xxxxxxxxxx>]

Below test results into Loop:

 

1.create test table

CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT);

 

2.create trigger function

CREATE OR REPLACE FUNCTION TRIG_FUNC () RETURNS TRIGGER AS

$$

DECLARE

PSQL VARCHAR2;

BEGIN

Raise info 'This is Test!!!';

psql:= 'INSERT INTO TEST_TABLE VALUES(''john'', 25);';

execute psql;

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

 

3.create trigger

CREATE TRIGGER TEST_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON TEST_TABLE FOR EACH ROW

EXECUTE PROCEDURE TRIG_FUNC ();

 

4.Perform an insert statement

INSERT INTO TEST_TABLE VALUES（'jack',25);

 

Now, You will see an always loop.

I understand that user can change his code to make it proper.

However shouldn’t PostgreSQL also throws errors in such cases for recursion level or something related?

 

With Regards,

Amit Kapila.