Tests for PostgREST new proposed query for parent embeds

parent_join_test_query_new.sql

-- Prior 9.6 \setrandom :rid 1 999900
\set rid random(1, 999900)

BEGIN ISOLATION LEVEL READ COMMITTED READ ONLY;

WITH pg_source AS
  (SELECT "parent_query_test"."items"."id",
          "parent_query_test"."items"."name",
          row_to_json("parent_items_parent_items".*) AS "parent_items"
   FROM "parent_query_test"."items"
   LEFT JOIN LATERAL
     (SELECT "parent_query_test"."parent_items"."id", 
             "parent_query_test"."parent_items"."name"
      FROM "parent_query_test"."parent_items"
      WHERE "parent_items"."id" = "items"."parent_id" ) AS "parent_items_parent_items" ON TRUE
   WHERE "parent_query_test"."items"."id" > :rid AND
         "parent_query_test"."items"."id" <= (:rid + 100))
SELECT NULL AS total_result_set,
       pg_catalog.count(_postgrest_t) AS page_total, array[]::text[] AS header,
       coalesce(array_to_json(array_agg(row_to_json(_postgrest_t))), '[]')::character varying AS BODY
FROM (SELECT * FROM pg_source) _postgrest_t;

COMMIT;

parent_join_test_query_old.sql

-- Prior 9.6 \setrandom :rid 1 999900
\set rid random(1, 999900)

BEGIN ISOLATION LEVEL READ COMMITTED READ ONLY;

WITH pg_source AS
  (SELECT "parent_query_test"."items"."id",
          "parent_query_test"."items"."name",
          row_to_json("parent_items_parent_items".*) AS "parent_items"
   FROM "parent_query_test"."items"
   LEFT OUTER JOIN
     (SELECT "parent_query_test"."parent_items"."id",
             "parent_query_test"."parent_items"."name"
      FROM "parent_query_test"."parent_items") AS "parent_items_parent_items" 
   ON "parent_items_parent_items"."id" = "items"."parent_id"
   WHERE "parent_query_test"."items"."id" > :rid AND 
         "parent_query_test"."items"."id" <= (:rid + 100))
SELECT NULL AS total_result_set,
       pg_catalog.count(_postgrest_t) AS page_total, array[]::text[] AS header,
       coalesce(array_to_json(array_agg(row_to_json(_postgrest_t))), '[]')::character varying AS BODY
FROM (SELECT * FROM pg_source) _postgrest_t;

COMMIT;

parent_join_test_schema.sql

DROP SCHEMA IF EXISTS parent_query_test CASCADE;

CREATE SCHEMA parent_query_test;

SET search_path = parent_query_test;

CREATE TABLE parent_items (
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE items (
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id INT NOT NULL REFERENCES parent_items(id)
);

CREATE OR REPLACE FUNCTION populate() RETURNS VOID AS $$
BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT INTO parent_items VALUES(i, 'parent_item ' || i);
  END LOOP;

  FOR i IN 1..1000000 LOOP
    INSERT INTO items VALUES(i, 'item ' || i, (i / 100) + 1);
  END LOOP;
END;
$$ LANGUAGE PLPGSQL;

SELECT populate();