--
-- Test trigger capabilities
--
CREATE FUNCTION trigfunc() RETURNS trigger LANGUAGE plphp AS $$
	$extra = "";
	if ($_TD['level'] == 'ROW') {
		if ($_TD['event'] == 'DELETE' || $_TD['event'] == 'UPDATE') {
			$extra .= " old val: ". $_TD['old']['a'];
		}
		if ($_TD['event'] == 'INSERT' || $_TD['event'] == 'UPDATE') {
			$extra .= " new val: ". $_TD['new']['a'];
		}
	}
	
	pg_raise('notice', "trigger: {$_TD['name']} table: {$_TD['relname']}, ".
		 "level: {$_TD['level']} event: {$_TD['when']} {$_TD['event']}$extra");
$$;
create table trigfunc_table (a int);
create trigger trigtest0 before insert on trigfunc_table for each row execute procedure trigfunc();
create trigger trigtest1 before update on trigfunc_table for each row execute procedure trigfunc();
create trigger trigtest2 before delete on trigfunc_table for each row execute procedure trigfunc();
create trigger trigtest3 before insert on trigfunc_table for each statement execute procedure trigfunc();
create trigger trigtest4 before update on trigfunc_table for each statement execute procedure trigfunc();
create trigger trigtest5 before delete on trigfunc_table for each statement execute procedure trigfunc();
create trigger trigtest6 after insert on trigfunc_table for each row execute procedure trigfunc();
create trigger trigtest7 after update on trigfunc_table for each row execute procedure trigfunc();
create trigger trigtest8 after delete on trigfunc_table for each row execute procedure trigfunc();
create trigger trigtest9 after insert on trigfunc_table for each statement execute procedure trigfunc();
create trigger trigtest10 after update on trigfunc_table for each statement execute procedure trigfunc();
create trigger trigtest11 after delete on trigfunc_table for each statement execute procedure trigfunc();
-- insert two rows with a=1 so we can see the UPDATE per-row trigger
-- firing twice
insert into trigfunc_table values (1);
NOTICE:  plphp: trigger: trigtest3 table: trigfunc_table, level: STATEMENT event: BEFORE INSERT
NOTICE:  plphp: trigger: trigtest0 table: trigfunc_table, level: ROW event: BEFORE INSERT new val: 1
NOTICE:  plphp: trigger: trigtest6 table: trigfunc_table, level: ROW event: AFTER INSERT new val: 1
NOTICE:  plphp: trigger: trigtest9 table: trigfunc_table, level: STATEMENT event: AFTER INSERT
insert into trigfunc_table values (1);
NOTICE:  plphp: trigger: trigtest3 table: trigfunc_table, level: STATEMENT event: BEFORE INSERT
NOTICE:  plphp: trigger: trigtest0 table: trigfunc_table, level: ROW event: BEFORE INSERT new val: 1
NOTICE:  plphp: trigger: trigtest6 table: trigfunc_table, level: ROW event: AFTER INSERT new val: 1
NOTICE:  plphp: trigger: trigtest9 table: trigfunc_table, level: STATEMENT event: AFTER INSERT
insert into trigfunc_table values (2);
NOTICE:  plphp: trigger: trigtest3 table: trigfunc_table, level: STATEMENT event: BEFORE INSERT
NOTICE:  plphp: trigger: trigtest0 table: trigfunc_table, level: ROW event: BEFORE INSERT new val: 2
NOTICE:  plphp: trigger: trigtest6 table: trigfunc_table, level: ROW event: AFTER INSERT new val: 2
NOTICE:  plphp: trigger: trigtest9 table: trigfunc_table, level: STATEMENT event: AFTER INSERT
update trigfunc_table set a = 2 where a = 1;
NOTICE:  plphp: trigger: trigtest4 table: trigfunc_table, level: STATEMENT event: BEFORE UPDATE
NOTICE:  plphp: trigger: trigtest1 table: trigfunc_table, level: ROW event: BEFORE UPDATE old val: 1 new val: 2
NOTICE:  plphp: trigger: trigtest1 table: trigfunc_table, level: ROW event: BEFORE UPDATE old val: 1 new val: 2
NOTICE:  plphp: trigger: trigtest7 table: trigfunc_table, level: ROW event: AFTER UPDATE old val: 1 new val: 2
NOTICE:  plphp: trigger: trigtest7 table: trigfunc_table, level: ROW event: AFTER UPDATE old val: 1 new val: 2
NOTICE:  plphp: trigger: trigtest10 table: trigfunc_table, level: STATEMENT event: AFTER UPDATE
delete from trigfunc_table where a = 2;
NOTICE:  plphp: trigger: trigtest5 table: trigfunc_table, level: STATEMENT event: BEFORE DELETE
NOTICE:  plphp: trigger: trigtest2 table: trigfunc_table, level: ROW event: BEFORE DELETE old val: 2
NOTICE:  plphp: trigger: trigtest2 table: trigfunc_table, level: ROW event: BEFORE DELETE old val: 2
NOTICE:  plphp: trigger: trigtest2 table: trigfunc_table, level: ROW event: BEFORE DELETE old val: 2
NOTICE:  plphp: trigger: trigtest8 table: trigfunc_table, level: ROW event: AFTER DELETE old val: 2
NOTICE:  plphp: trigger: trigtest8 table: trigfunc_table, level: ROW event: AFTER DELETE old val: 2
NOTICE:  plphp: trigger: trigtest8 table: trigfunc_table, level: ROW event: AFTER DELETE old val: 2
NOTICE:  plphp: trigger: trigtest11 table: trigfunc_table, level: STATEMENT event: AFTER DELETE
-- Do we get the schema?
CREATE FUNCTION trigfunc2() RETURNS trigger LANGUAGE plphp AS $$
	$extra = "";
	
	pg_raise('notice', "schema: {$_TD['schemaname']} table: {$_TD['relname']}");
$$;
create schema "the schema" create table "the table" (a int);
create trigger trig2test after insert on "the schema"."the table" for each row execute procedure trigfunc2();
insert into "the schema"."the table" values (4);
NOTICE:  plphp: schema: the schema table: the table
-- Add an additional attribute to NEW
CREATE FUNCTION trigfunc_test1() RETURNS trigger LANGUAGE plphp AS $$
	$_TD['new']['a'] = 'foo';
	return 'MODIFY';
$$;
CREATE TABLE trigfunc_test1 (b int);
CREATE TRIGGER trig_test1 BEFORE INSERT ON trigfunc_test1
FOR EACH ROW EXECUTE PROCEDURE trigfunc_test1();
-- NEW has an incorrect number of keys
INSERT INTO trigfunc_test1 VALUES (1);
-- Create NEW as a new array, with a single attribute that's not the expected
-- one
CREATE OR REPLACE FUNCTION trigfunc_test1() RETURNS trigger LANGUAGE plphp AS $$
	$_TD['new'] = array('a' => 'foo');
	return 'MODIFY';
$$;
-- Invalid attribute "a" in NEW
INSERT INTO trigfunc_test1 VALUES (1);
ERROR:  $_TD['new'] does not contain attribute "b"
CONTEXT:  PL/php function "trigfunc_test1"
-- Create NEW as the correct array, but with a NULL value
CREATE OR REPLACE FUNCTION trigfunc_test1() RETURNS trigger LANGUAGE plphp AS $$
	$_TD['new'] = array('b' => NULL);
	return 'MODIFY';
$$;
INSERT INTO trigfunc_test1 VALUES (1);
-- should have inserted a NULL
SELECT b IS NULL FROM trigfunc_test1;
 ?column? 
----------
 f
 t
(2 rows)

DELETE FROM trigfunc_test1;
-- Return an empty array
CREATE OR REPLACE FUNCTION trigfunc_test1() RETURNS trigger LANGUAGE plphp AS $$
	$_TD['new'] = array();
	return 'MODIFY';
$$;
INSERT INTO trigfunc_test1 VALUES (1);
ERROR:  insufficient number of keys in $_TD['new']
DETAIL:  At least 1 expected, 0 found.
CONTEXT:  PL/php function "trigfunc_test1"
-- Make "b" a literal of an incorrect type
CREATE OR REPLACE FUNCTION trigfunc_test1() RETURNS trigger LANGUAGE plphp AS $$
	$_TD['new'] = array('b' => 'foobar');
	return 'MODIFY';
$$;
INSERT INTO trigfunc_test1 VALUES (1);
ERROR:  invalid input syntax for integer: "foobar"
CONTEXT:  PL/php function "trigfunc_test1"
-- Make "b" an array
CREATE OR REPLACE FUNCTION trigfunc_test1() RETURNS trigger LANGUAGE plphp AS $$
	$_TD['new'] = array('b' => array(1, 2, 3));
	return 'MODIFY';
$$;
INSERT INTO trigfunc_test1 VALUES (1);
ERROR:  invalid input syntax for integer: "{1,2,3}"
CONTEXT:  PL/php function "trigfunc_test1"
-- What happens if we create a table whose columns are numbers?
-- Does PHP regular array handling work?
CREATE TABLE numbers ("0" int, "1" text, "2" float, "3" numeric[]);
CREATE OR REPLACE FUNCTION numbers_trig() RETURNS TRIGGER LANGUAGE plphp AS $$
	$_TD['new'] = array(5, 'hello', 1.4142,
		array(-123, 14 * 3, 142857 * 2));
	RETURN 'MODIFY';
$$;
CREATE TRIGGER numbers_trig BEFORE INSERT ON numbers
FOR EACH ROW EXECUTE PROCEDURE numbers_trig();
INSERT INTO numbers DEFAULT VALUES;
SELECT * FROM numbers;
 0 |   1   |   2    |        3         
---+-------+--------+------------------
 5 | hello | 1.4142 | {-123,42,285714}
(1 row)

-- Now use strings with the names of the columns
CREATE OR REPLACE FUNCTION numbers_trig() RETURNS TRIGGER LANGUAGE plphp AS $$
	$_TD['new'] = array("0" => 42, "1" => 'hello new jersey', "2" => 3.14,
		"3" => array(array(1,7),array(2,3),array(4,8),array(5,6)));
	RETURN 'MODIFY';
$$;
INSERT INTO numbers DEFAULT VALUES;
SELECT * FROM numbers;
 0  |        1         |   2    |             3             
----+------------------+--------+---------------------------
  5 | hello            | 1.4142 | {-123,42,285714}
 42 | hello new jersey |   3.14 | {{1,7},{2,3},{4,8},{5,6}}
(2 rows)

DROP TABLE numbers;
-- Test the relid and relname attributes of $_TD
CREATE TABLE test_rel (tbl name, relid oid);
CREATE FUNCTION test_rel() RETURNS TRIGGER LANGUAGE plphp AS $$
	$_TD['new']['tbl'] = $_TD['relname'];
	$_TD['new']['relid'] = $_TD['relid'];
	return 'MODIFY';
$$;
CREATE TRIGGER test_rel BEFORE INSERT ON test_rel
FOR EACH ROW EXECUTE PROCEDURE test_rel();
INSERT INTO test_rel values ('foo', '0');
SELECT tbl = 'test_rel', relid = 'test_rel'::regclass FROM test_rel;
 ?column? | ?column? 
----------+----------
 t        | t
(1 row)

-- Test consistency checks in $_TD
create table foo (foo_id int primary key, descrip text not null);
create or replace function foo_trig() returns trigger
language plphp as $$
	$_TD = 1;
	return 'MODIFY';
$$;
create trigger foo before insert on foo
for each row execute procedure foo_trig();
insert into foo values (1, 'one');
ERROR:  $_TD is not an array
CONTEXT:  PL/php function "foo_trig"
create or replace function foo_trig() returns trigger
language plphp as $$
	$_TD = array();
	return 'MODIFY';
$$;
insert into foo values (1, 'one');
ERROR:  $_TD['new'] not found
CONTEXT:  PL/php function "foo_trig"
create or replace function foo_trig() returns trigger
language plphp as $$
	$_TD = array('new' => 1);
	return 'MODIFY';
$$;
insert into foo values (1, 'one');
ERROR:  $_TD['new'] must be an array
CONTEXT:  PL/php function "foo_trig"
create or replace function foo_trig() returns trigger
language plphp as $$
	$_TD = array('new' => array());
	return 'MODIFY';
$$;
insert into foo values (1, 'one');
ERROR:  insufficient number of keys in $_TD['new']
DETAIL:  At least 2 expected, 0 found.
CONTEXT:  PL/php function "foo_trig"