--
-- 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
$$;

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
$$;

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
$$;

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
$$;

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
$$;

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
$$;

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
$$;

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
$$;

SELECT * FROM collection_regress ORDER BY col1;

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
$$;

-- 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 $$;

-- 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 $$;

-- ============================================================
-- 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
$$;

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
$$;

-- 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
$$;

-- 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
$$;

-- 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
$$;

-- 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
$$;

-- 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
$$;

-- 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 $$;

-- 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 $$;
