-- array conversion hardening: NULL elements, escaping, unquoted text,
-- numeric element types, empty arrays, bytea round-trips
-- returning an array containing a PHP null produces a SQL NULL element
create function arr_with_null() returns int[] language plphp as $$
	return array(1, null, 3);
$$;
select arr_with_null();
 arr_with_null 
---------------
 {1,NULL,3}
(1 row)

-- text elements needing quoting/escaping survive the round trip out...
create function arr_specials_out() returns text[] language plphp as $$
	return array('plain', 'has"quote', 'back\\slash', 'com,ma', '{brace}', 'sp ace', '');
$$;
select arr_specials_out();
                         arr_specials_out                          
-------------------------------------------------------------------
 {plain,"has\"quote","back\\slash","com,ma","{brace}","sp ace",""}
(1 row)

-- ... and back in again (returns each element base64'd so the diff is unambiguous)
create function arr_specials_in(text[]) returns text language plphp as $$
	$out = array();
	foreach ($args[0] as $el)
		$out[] = $el === null ? "NULL" : base64_encode($el);
	return implode(" ", $out);
$$;
select arr_specials_in(array['plain', 'has"quote', 'back\slash', 'com,ma', '{brace}', 'sp ace', '', null]);
                               arr_specials_in                               
-----------------------------------------------------------------------------
 cGxhaW4= aGFzInF1b3Rl YmFja1xzbGFzaA== Y29tLG1h e2JyYWNlfQ== c3AgYWNl  NULL
(1 row)

-- unquoted text elements (PG quotes only when needed) arrive as strings
create function arr_unquoted(text[]) returns text language plphp as $$
	return implode("|", $args[0]) . " (" . gettype($args[0][0]) . ")";
$$;
select arr_unquoted(array['foo', 'bar', 'baz']);
     arr_unquoted     
----------------------
 foo|bar|baz (string)
(1 row)

-- numeric arrays keep PHP numeric types
create function arr_types(int[], float8[]) returns text language plphp as $$
	return gettype($args[0][0]) . " " . gettype($args[1][0]);
$$;
select arr_types(array[1, 2], array[1.5, 2.5]);
   arr_types    
----------------
 integer double
(1 row)

-- int array containing a NULL on input
create function arr_null_in(int[]) returns int language plphp as $$
	$sum = 0; $nulls = 0;
	foreach ($args[0] as $el)
	{
		if ($el === null) $nulls++;
		else $sum += $el;
	}
	return $sum * 10 + $nulls;
$$;
select arr_null_in(array[1, null, 3]);
 arr_null_in 
-------------
          41
(1 row)

-- empty arrays, both directions
create function arr_empty_out() returns int[] language plphp as $$
	return array();
$$;
select arr_empty_out();
 arr_empty_out 
---------------
 {}
(1 row)

create function arr_empty_in(int[]) returns int language plphp as $$
	return count($args[0]);
$$;
select arr_empty_in('{}');
 arr_empty_in 
--------------
            0
(1 row)

-- multi-dimensional array of strings needing quoting
create function arr_ndim_text() returns text[] language plphp as $$
	return array(array('a"b', 'c,d'), array('{e}', 'f\\g'));
$$;
select arr_ndim_text();
          arr_ndim_text          
---------------------------------
 {{"a\"b","c,d"},{"{e}","f\\g"}}
(1 row)

-- multi-dimensional input indexes correctly
create function arr_ndim_in(text[]) returns text language plphp as $$
	return $args[0][1][0];
$$;
select arr_ndim_in(array[array['a', 'b'], array['see', 'd']]);
 arr_ndim_in 
-------------
 see
(1 row)

-- bytea round-trip: PL/php sees the \x hex output form
create function bytea_roundtrip(bytea) returns bytea language plphp as $$
	return $args[0];
$$;
select bytea_roundtrip('\x0001ff68656c6c6f');
  bytea_roundtrip   
--------------------
 \x0001ff68656c6c6f
(1 row)

create function bytea_make(text) returns bytea language plphp as $$
	return "\\x" . bin2hex($args[0]);
$$;
select bytea_make('hi there');
     bytea_make     
--------------------
 \x6869207468657265
(1 row)

create function bytea_read(bytea) returns text language plphp as $$
	return strtoupper(substr($args[0], 2));
$$;
select bytea_read('\xdeadbeef');
 bytea_read 
------------
 DEADBEEF
(1 row)

-- array-typed columns inside rows arrive as PHP arrays (not "{...}" strings):
-- via SPI rows...
create table arrt (id int, tags text[], nums int[]);
insert into arrt values (1, array['red', 'b"lue'], array[10, 20, 30]);
create function arr_in_row() returns text language plphp as $$
	$r = spi_exec("select * from arrt");
	$row = spi_fetch_row($r);
	return gettype($row['tags']) . " " . $row['tags'][1] . " " . array_sum($row['nums']);
$$;
select arr_in_row();
   arr_in_row   
----------------
 array b"lue 60
(1 row)

-- ...via cursor rows...
create function arr_in_cursor() returns int language plphp as $$
	$c = spi_query("select nums from arrt");
	$row = spi_fetchrow($c);
	return count($row['nums']);
$$;
select arr_in_cursor();
 arr_in_cursor 
---------------
             3
(1 row)

-- ...in $_TD for triggers, and writable back through MODIFY
create function arr_trig() returns trigger language plphp as $$
	pg_raise('notice', 'tags is ' . gettype($_TD['new']['tags'])
		. ' with ' . count($_TD['new']['tags']) . ' elements');
	$_TD['new']['tags'][] = 'added';
	return 'MODIFY';
$$;
create trigger arrt_trg before insert on arrt
	for each row execute procedure arr_trig();
insert into arrt values (2, array['green'], array[1]);
NOTICE:  plphp: tags is array with 1 elements
select tags from arrt where id = 2;
     tags      
---------------
 {green,added}
(1 row)

drop trigger arrt_trg on arrt;
-- ...and in composite-type arguments
create type with_arr as (label text, vals int[]);
create function arr_in_comp(with_arr) returns int language plphp as $$
	return array_sum($args[0]['vals']);
$$;
select arr_in_comp(row('x', array[5, 6, 7])::with_arr);
 arr_in_comp 
-------------
          18
(1 row)

drop function arr_in_row(), arr_in_cursor(), arr_in_comp(with_arr), arr_trig();
drop type with_arr;
drop table arrt;
-- composite conversion: composite columns/fields/elements are associative
-- arrays in both directions
create type arr_addr as (street text, city text);
create type arr_person as (name text, home arr_addr, tags text[]);
create table arr_folks (id int, who arr_person);
insert into arr_folks values
  (1, row('J''s "Place"', row('1 Main, Apt (2)', 'Olympia')::arr_addr,
          array['a', 'b,c'])::arr_person);
-- composite column in a row: nested composite and array fields convert
create function arr_rec_peek() returns text language plphp as $$
	$r = spi_exec("select * from arr_folks");
	$row = spi_fetch_row($r);
	$w = $row['who'];
	return gettype($w) . " | " . $w['name'] . " | " . $w['home']['city']
		. " | " . $w['tags'][1] . " | " . gettype($w['tags']);
$$;
select arr_rec_peek();
                arr_rec_peek                 
---------------------------------------------
 array | J's "Place" | Olympia | b,c | array
(1 row)

-- composite argument (with nesting)
create function arr_rec_arg(p arr_person) returns text language plphp as $$
	return $args[0]['home']['street'] . " / " . count($args[0]['tags']);
$$;
select arr_rec_arg(row('X', row('5 Oak', 'Tacoma')::arr_addr, array['q'])::arr_person);
 arr_rec_arg 
-------------
 5 Oak / 1
(1 row)

-- composites inside an array argument
create function arr_rec_elems(addrs arr_addr[]) returns text language plphp as $$
	return $args[0][1]['city'];
$$;
select arr_rec_elems(array[row('a', 'CityA')::arr_addr, row('b', 'City,B()')::arr_addr]);
 arr_rec_elems 
---------------
 City,B()
(1 row)

-- the reverse: build a nested composite from associative arrays
create function arr_rec_make() returns arr_person language plphp as $$
	return array(
		'name' => 'He said "hi"',
		'home' => array('street' => '9 Elm, S(2)', 'city' => 'B\\C'),
		'tags' => array('x', 'y z')
	);
$$;
select * from arr_rec_make();
     name     |          home          |   tags    
--------------+------------------------+-----------
 He said "hi" | ("9 Elm, S(2)","B\\C") | {x,"y z"}
(1 row)

select (arr_rec_make()).home.city, (arr_rec_make()).tags[2];
 city | tags 
------+------
 B\C  | y z
(1 row)

-- trigger MODIFY through a composite column
create function arr_rec_trig() returns trigger language plphp as $$
	$_TD['new']['who']['home']['city'] = 'Changed';
	return 'MODIFY';
$$;
create trigger arr_folks_trg before insert on arr_folks
	for each row execute procedure arr_rec_trig();
insert into arr_folks values
  (2, row('K', row('7 Pine', 'Old')::arr_addr, array['z'])::arr_person);
select (who).home.city from arr_folks where id = 2;
  city   
---------
 Changed
(1 row)

drop trigger arr_folks_trg on arr_folks;
-- NULL fields inside composites survive both directions
create function arr_rec_nulls(p arr_addr) returns arr_addr language plphp as $$
	pg_raise('notice', 'street is ' . ($args[0]['street'] === null ? 'null' : 'set'));
	return array('street' => null, 'city' => $args[0]['city']);
$$;
select * from arr_rec_nulls(row(NULL, 'Salem')::arr_addr);
NOTICE:  plphp: street is null
 street | city  
--------+-------
        | Salem
(1 row)

drop function arr_rec_peek(), arr_rec_arg(arr_person), arr_rec_elems(arr_addr[]),
              arr_rec_make(), arr_rec_trig(), arr_rec_nulls(arr_addr);
drop table arr_folks;
drop type arr_person, arr_addr;
-- anycompatible polymorphics (PostgreSQL 13+)
create function arr_first_compat(anycompatiblearray) returns anycompatible
language plphp as $$
	return $args[0][0];
$$;
select arr_first_compat(array[7, 8, 9]);
 arr_first_compat 
------------------
                7
(1 row)

select arr_first_compat(array['x', 'y']);
 arr_first_compat 
------------------
 x
(1 row)