Examples of queries built up using WITH statements

with.sql

-- JSON FESTIVAL

WITH pg_source AS
  (SELECT "public"."festival".* FROM "public"."festival")
SELECT
  (SELECT pg_catalog.Count(1) FROM "public"."festival")       AS total_result_set,
  pg_catalog.Count(t)                                         AS page_total,
  NULL                                                        AS header,
  Array_to_json(Array_agg(Row_to_json(t)))::character VARYING AS body
FROM
  (SELECT * FROM pg_source LIMIT ALL OFFSET 0) t

-- JSON FESTIVAL FILTERED

WITH pg_source AS
  (SELECT "public"."festival".* FROM "public"."festival"
   WHERE "public"."festival"."name" LIKE 'fun'::UNKNOWN)
SELECT
  (SELECT pg_catalog.count(1) FROM "public"."festival"
   WHERE "public"."festival"."name" LIKE 'fun'::UNKNOWN)      AS total_result_set,
  pg_catalog.count(t)                                         AS page_total,
  NULL                                                        AS header,
  array_to_json(array_agg(row_to_json(t)))::character varying AS body
FROM
  (SELECT *
   FROM pg_source LIMIT ALL
   OFFSET 0) t

-- JSON FESTIVAL WITHOUT COUNT

WITH pg_source AS
  (SELECT "public"."festival".* FROM "public"."festival")
SELECT
  NULL                                                        AS total_result_set,
  pg_catalog.count(t)                                         AS page_total,
  NULL                                                        AS header,
  array_to_json(array_agg(row_to_json(t)))::character varying AS body
FROM
  (SELECT * FROM pg_source LIMIT ALL OFFSET 0) t

-- CSV FESTIVAL

WITH pg_source AS
  (SELECT "public"."festival".* FROM "public"."festival")
SELECT
  (SELECT pg_catalog.count(1) FROM "public"."festival")       AS total_result_set,
  pg_catalog.count(t)                                         AS page_total,
  NULL                                                        AS header,

  (SELECT string_agg(a.k, ',')
   FROM
     (SELECT json_object_keys(r)::TEXT AS k
      FROM
        (SELECT row_to_json(hh) AS r
         FROM pg_source AS hh LIMIT 1) s
     ) a
  ) || '\n' ||
  coalesce(
    string_agg(
      substring(t::text, 2, length(t::text) - 2), '\n'
    ), ''
  )                                                           AS body
FROM
  (SELECT * FROM pg_source LIMIT ALL OFFSET 0) t

-- JSON MOVIE ID WITH DIRECTOR

WITH pg_source AS
  (SELECT "public"."film"."id", row_to_json("director".*) AS "director"
   FROM "public"."film"
   LEFT OUTER JOIN
     (SELECT "public"."director".*
      FROM "public"."director") AS "director" ON "director"."name" = "film"."director")
SELECT
  (SELECT pg_catalog.count(1) FROM "public"."film")           AS total_result_set,
  pg_catalog.count(t)                                         AS page_total,
  NULL                                                        AS header,
  array_to_json(array_agg(row_to_json(t)))::character varying AS body
FROM
  (SELECT * FROM pg_source LIMIT ALL OFFSET 0) t

-- JSON COMPETITIONS WITH THEIR MOVIES

WITH pg_source AS
  (SELECT
   "public"."competition"."name",
   COALESCE (
     (SELECT array_to_json(array_agg(row_to_json("film")))
      FROM
        (SELECT "public"."film".*
         FROM "public"."film_nomination",
              "public"."film"
         WHERE "public"."film"."id" = "public"."film_nomination"."film"
           AND "public"."competition"."id" = "public"."film_nomination"."competition") "film"
      ), '[]'
   ) AS "film"
   FROM "public"."competition")
SELECT
  (SELECT pg_catalog.count(1) FROM "public"."competition")    AS total_result_set,
  pg_catalog.count(t)                                         AS page_total,
  NULL                                                        AS header,
  array_to_json(array_agg(row_to_json(t)))::character varying AS body
FROM
  (SELECT * FROM pg_source LIMIT ALL OFFSET 0) t


-- GENERALIZED ----------------------------------------------------------------------

with foo as ($FOO)
select total_result_set, page_total, header, body
from (select * from foo [limit offset]) t