--
-- srf.sql
--     Set-returning functions: keys_to_table, values_to_table, to_table.
--     Covers both collection (text-keyed) and icollection (int-keyed).
--
-- ============================================================
-- PART 1: collection SRF
-- ============================================================
DO $$
DECLARE
  u   collection('text');
  r   record;
BEGIN
  RAISE NOTICE 'SRF test 1';
  u['aaa'] := 'Hello World';
  u['bbb'] := 'Hello All';

  RAISE NOTICE '----------------';
  FOR r IN SELECT k FROM keys_to_table(u) k
  LOOP
    RAISE NOTICE 'key: [%]', r.k;
  END LOOP;
END
$$;
NOTICE:  SRF test 1
NOTICE:  ----------------
NOTICE:  key: [aaa]
NOTICE:  key: [bbb]
DO $$
DECLARE
  u   collection('text');
  r   record;
BEGIN
  RAISE NOTICE 'SRF test 2';
  u['aaa'] := 'Hello World';
  u['bbb'] := 'Hello All';

  RAISE NOTICE '----------------';
  FOR r IN SELECT v FROM values_to_table(u) v
  LOOP
    RAISE NOTICE 'value: [%]', r.v;
  END LOOP;
END
$$;
NOTICE:  SRF test 2
NOTICE:  ----------------
NOTICE:  value: [Hello World]
NOTICE:  value: [Hello All]
DO $$
DECLARE
  u   collection('date');
  r   record;
BEGIN
  RAISE NOTICE 'SRF test 3';
  u['aaa'] := '1999-12-31'::date;
  u['bbb'] := '2000-01-01'::date;

  RAISE NOTICE '----------------';
  FOR r IN SELECT v FROM values_to_table(u, null::date) v
  LOOP
    RAISE NOTICE 'value: [%]', r.v;
  END LOOP;
END
$$;
NOTICE:  SRF test 3
NOTICE:  ----------------
NOTICE:  value: [12-31-1999]
NOTICE:  value: [01-01-2000]
DO $$
DECLARE
  u   collection('int');
  r   record;
BEGIN
  RAISE NOTICE 'SRF test 3';
  u['aa'] := 100;
  u['bb'] := 222;

  RAISE NOTICE '----------------';
  FOR r IN SELECT v FROM values_to_table(u, null::int) v
  LOOP
    RAISE NOTICE 'value: [%]', r.v;
  END LOOP;
END
$$;
NOTICE:  SRF test 3
NOTICE:  ----------------
NOTICE:  value: [100]
NOTICE:  value: [222]
DO $$
DECLARE
  u   collection('text');
  r   record;
BEGIN
  RAISE NOTICE 'SRF test 4';
  u['aaa'] := 'Hello World';
  u['bbb'] := 'Hello All';

  RAISE NOTICE '----------------';
  FOR r IN SELECT * FROM to_table(u) v
  LOOP
	RAISE NOTICE 'key: [%] value: [%]', r.key, r.value;
  END LOOP;
END
$$;
NOTICE:  SRF test 4
NOTICE:  ----------------
NOTICE:  key: [aaa] value: [Hello World]
NOTICE:  key: [bbb] value: [Hello All]
DO $$
DECLARE
  u   collection('date');
  r   record;
BEGIN
  RAISE NOTICE 'SRF test 5';
  u['aaa'] := '1999-12-31'::date;
  u['bbb'] := '2000-01-01'::date;

  RAISE NOTICE '----------------';
  FOR r IN SELECT * FROM to_table(u, null::date) v
  LOOP
	RAISE NOTICE 'key: [%] value: [%]', r.key, r.value;
  END LOOP;
END
$$;
NOTICE:  SRF test 5
NOTICE:  ----------------
NOTICE:  key: [aaa] value: [12-31-1999]
NOTICE:  key: [bbb] value: [01-01-2000]
CREATE TABLE collection_regress(col1 varchar, col2 varchar, col3 int);
INSERT INTO collection_regress VALUES ('aaa', 'Hello World', 42), ('bbb', 'Hello All', 84);
DO
$$
DECLARE
  r       collection_regress%ROWTYPE;
  c       collection('collection_regress');
  cr      record;
BEGIN
  RAISE NOTICE 'SRF test 6';
  FOR r IN SELECT * 
             FROM collection_regress 
            ORDER BY col1
  LOOP
    c[r.col1] = r;
  END LOOP;

  FOR cr IN SELECT * FROM to_table(c, null::collection_regress)
  LOOP
    RAISE NOTICE 'col1: [%] col3: [%]', cr.key, (cr.value).col3;
  END LOOP;
END
$$;
NOTICE:  SRF test 6
NOTICE:  col1: [aaa] col3: [42]
NOTICE:  col1: [bbb] col3: [84]
DO
$$
DECLARE
  c       collection('int');
BEGIN
  RAISE NOTICE 'SRF test 7';

  c['aaa'] := 142;
  c['bbb'] := 184;

  UPDATE collection_regress
     SET col3 = t.value
    FROM to_table(c, null::int) t
   WHERE col1 = t.key;
END
$$;
NOTICE:  SRF test 7
SELECT * FROM collection_regress ORDER BY col1;
 col1 |    col2     | col3 
------+-------------+------
 aaa  | Hello World |  142
 bbb  | Hello All   |  184
(2 rows)

DROP TABLE collection_regress;
DO $$
DECLARE
  u   collection('date');
  r   record;
BEGIN
  RAISE NOTICE 'SRF test 8';
  u['aaa'] := '1999-12-31'::date;
  u['bbb'] := null;
  u['ccc'] := '2000-01-01'::date;

  RAISE NOTICE '----------------';
  FOR r IN SELECT * FROM to_table(u, null::date) v
  LOOP
	RAISE NOTICE 'key: [%] value: [%]', r.key, r.value;
  END LOOP;
END
$$;
NOTICE:  SRF test 8
NOTICE:  ----------------
NOTICE:  key: [aaa] value: [12-31-1999]
NOTICE:  key: [bbb] value: [<NULL>]
NOTICE:  key: [ccc] value: [01-01-2000]
-- SRF on empty collection
DO $$
DECLARE
  c collection;
  cnt int := 0;
  r record;
BEGIN
  RAISE NOTICE 'SRF test 9 - empty collection';
  c := add(c, 'a', 'val');
  c := delete(c, 'a');

  FOR r IN SELECT k FROM keys_to_table(c) k LOOP cnt := cnt + 1; END LOOP;
  ASSERT cnt = 0, 'keys_to_table on empty should return 0 rows';

  FOR r IN SELECT v FROM values_to_table(c) v LOOP cnt := cnt + 1; END LOOP;
  ASSERT cnt = 0, 'values_to_table on empty should return 0 rows';

  FOR r IN SELECT * FROM to_table(c) LOOP cnt := cnt + 1; END LOOP;
  ASSERT cnt = 0, 'to_table on empty should return 0 rows';
END $$;
NOTICE:  SRF test 9 - empty collection
-- SRF with NULL values
DO $$
DECLARE
  c collection;
  r record;
BEGIN
  RAISE NOTICE 'SRF test 10 - NULL values';
  c := add(c, 'a', 'real');
  c := add(c, 'b', null::text);
  c := add(c, 'c', 'also real');

  RAISE NOTICE '----------------';
  FOR r IN SELECT v FROM values_to_table(c) v
  LOOP
    RAISE NOTICE 'value: [%]', r.v;
  END LOOP;
END $$;
NOTICE:  SRF test 10 - NULL values
NOTICE:  ----------------
NOTICE:  value: [real]
NOTICE:  value: [<NULL>]
NOTICE:  value: [also real]
-- ============================================================
-- PART 2: icollection SRF
-- ============================================================
-- SRF tests for icollection (mirrors collection srf.sql)
DO $$
DECLARE
  ic   icollection('text');
  r    record;
BEGIN
  RAISE NOTICE 'SRF test 1';
  ic[1] := 'Hello World';
  ic[2] := 'Hello All';

  RAISE NOTICE '----------------';
  FOR r IN SELECT k FROM keys_to_table(ic) k
  LOOP
    RAISE NOTICE 'key: [%]', r.k;
  END LOOP;
END
$$;
NOTICE:  SRF test 1
NOTICE:  ----------------
NOTICE:  key: [1]
NOTICE:  key: [2]
DO $$
DECLARE
  ic   icollection('text');
  r    record;
BEGIN
  RAISE NOTICE 'SRF test 2';
  ic[1] := 'Hello World';
  ic[2] := 'Hello All';

  RAISE NOTICE '----------------';
  FOR r IN SELECT v FROM values_to_table(ic) v
  LOOP
    RAISE NOTICE 'value: [%]', r.v;
  END LOOP;
END
$$;
NOTICE:  SRF test 2
NOTICE:  ----------------
NOTICE:  value: [Hello World]
NOTICE:  value: [Hello All]
-- values_to_table with typed output (date)
DO $$
DECLARE
  ic   icollection('date');
  r    record;
BEGIN
  RAISE NOTICE 'SRF test 3';
  ic[1] := '1999-12-31'::date;
  ic[2] := '2000-01-01'::date;

  RAISE NOTICE '----------------';
  FOR r IN SELECT v FROM values_to_table(ic, null::date) v
  LOOP
    RAISE NOTICE 'value: [%]', r.v;
  END LOOP;
END
$$;
NOTICE:  SRF test 3
NOTICE:  ----------------
NOTICE:  value: [12-31-1999]
NOTICE:  value: [01-01-2000]
-- values_to_table with typed output (int)
DO $$
DECLARE
  ic   icollection('int');
  r    record;
BEGIN
  RAISE NOTICE 'SRF test 3b';
  ic[1] := 100;
  ic[2] := 222;

  RAISE NOTICE '----------------';
  FOR r IN SELECT v FROM values_to_table(ic, null::int) v
  LOOP
    RAISE NOTICE 'value: [%]', r.v;
  END LOOP;
END
$$;
NOTICE:  SRF test 3b
NOTICE:  ----------------
NOTICE:  value: [100]
NOTICE:  value: [222]
-- to_table text output
DO $$
DECLARE
  ic   icollection('text');
  r    record;
BEGIN
  RAISE NOTICE 'SRF test 4';
  ic[1] := 'Hello World';
  ic[2] := 'Hello All';

  RAISE NOTICE '----------------';
  FOR r IN SELECT * FROM to_table(ic) v
  LOOP
    RAISE NOTICE 'key: [%] value: [%]', r.key, r.value;
  END LOOP;
END
$$;
NOTICE:  SRF test 4
NOTICE:  ----------------
NOTICE:  key: [1] value: [Hello World]
NOTICE:  key: [2] value: [Hello All]
-- to_table typed output (date)
DO $$
DECLARE
  ic   icollection('date');
  r    record;
BEGIN
  RAISE NOTICE 'SRF test 5';
  ic[1] := '1999-12-31'::date;
  ic[2] := '2000-01-01'::date;

  RAISE NOTICE '----------------';
  FOR r IN SELECT * FROM to_table(ic, null::date) v
  LOOP
    RAISE NOTICE 'key: [%] value: [%]', r.key, r.value;
  END LOOP;
END
$$;
NOTICE:  SRF test 5
NOTICE:  ----------------
NOTICE:  key: [1] value: [12-31-1999]
NOTICE:  key: [2] value: [01-01-2000]
-- to_table with NULL values
DO $$
DECLARE
  ic   icollection('date');
  r    record;
BEGIN
  RAISE NOTICE 'SRF test 8';
  ic[1] := '1999-12-31'::date;
  ic[2] := null;
  ic[3] := '2000-01-01'::date;

  RAISE NOTICE '----------------';
  FOR r IN SELECT * FROM to_table(ic, null::date) v
  LOOP
    RAISE NOTICE 'key: [%] value: [%]', r.key, r.value;
  END LOOP;
END
$$;
NOTICE:  SRF test 8
NOTICE:  ----------------
NOTICE:  key: [1] value: [12-31-1999]
NOTICE:  key: [2] value: [<NULL>]
NOTICE:  key: [3] value: [01-01-2000]
-- SRF on empty icollection
DO $$
DECLARE
  ic icollection;
  cnt int := 0;
  r record;
BEGIN
  RAISE NOTICE 'ic SRF test - empty icollection';
  ic := add(ic, 1, 'val');
  ic := delete(ic, 1);

  FOR r IN SELECT k FROM keys_to_table(ic) k LOOP cnt := cnt + 1; END LOOP;
  ASSERT cnt = 0, 'ic keys_to_table on empty should return 0 rows';

  FOR r IN SELECT v FROM values_to_table(ic) v LOOP cnt := cnt + 1; END LOOP;
  ASSERT cnt = 0, 'ic values_to_table on empty should return 0 rows';

  FOR r IN SELECT * FROM to_table(ic) LOOP cnt := cnt + 1; END LOOP;
  ASSERT cnt = 0, 'ic to_table on empty should return 0 rows';
END $$;
NOTICE:  ic SRF test - empty icollection
-- SRF with NULL values
DO $$
DECLARE
  ic icollection;
  r record;
BEGIN
  RAISE NOTICE 'ic SRF test - NULL values';
  ic := add(ic, 1, 'real');
  ic := add(ic, 2, null::text);
  ic := add(ic, 3, 'also real');

  RAISE NOTICE '----------------';
  FOR r IN SELECT v FROM values_to_table(ic) v
  LOOP
    RAISE NOTICE 'value: [%]', r.v;
  END LOOP;
END $$;
NOTICE:  ic SRF test - NULL values
NOTICE:  ----------------
NOTICE:  value: [real]
NOTICE:  value: [<NULL>]
NOTICE:  value: [also real]