PATCH/DELETE based on an embedded table condition

[steve-chavez]

With Embedding with Top-level Filtering is now reasonable to do a PATCH/DELETE based on an embedded table condition, since it's an exact match.

This is not possible currently:

DELETE /projects?select=id,name,clients!inner(*)&clients.name=eq.Apple
Prefer:count=exact

HTTP/1.1 204 No Content
Content-Range: */0

We restrict update/delete filters just for the root table here, but this restriction can be lifted in cases where !inner is used.

---

Using the select is a bit confusing here, we'd need the exists filter for it to be clearer.

---

Using an empty embed is now possible so no more need for exists.

DELETE /projects?select=id,name,clients()&clients.name=eq.Apple&clients=not.is.null

[steve-chavez]

This is possible with the following query:

WITH pgrst_source AS (
  WITH
  pgrst_payload AS (SELECT '{"name": "Newnew"}'::json AS json_data),
  pgrst_body AS ( SELECT CASE WHEN json_typeof(json_data) = 'array' THEN json_data ELSE json_build_array(json_data) END AS val FROM pgrst_payload),
  pgrst_update_body AS (SELECT * FROM json_populate_recordset (null::"test"."projects" , (SELECT val FROM pgrst_body) ) LIMIT 1),
  pgrst_affected_rows AS (
    SELECT
      "projects"."id",
      "projects"."name",
      row_to_json("projects_clients_1".*) AS "clients"
    FROM "test"."projects" AS "projects"
    LEFT JOIN LATERAL (
      SELECT "clients_1"."name"
      FROM "test"."clients" AS "clients_1"
      WHERE
        "clients_1"."id" = "projects"."client_id" AND
        "clients_1"."name" = 'Apple'
    ) AS "projects_clients_1" ON TRUE
    WHERE "projects_clients_1" IS NOT NULL
  )
  UPDATE "test"."projects"
  SET "name" = (SELECT "name" FROM pgrst_update_body)
  FROM pgrst_affected_rows
  WHERE "test"."projects"."id" = "pgrst_affected_rows"."id"
  RETURNING "test"."projects"."client_id", "test"."projects"."id", "test"."projects"."name", "pgrst_affected_rows"."clients"
)
SELECT
  '' AS total_result_set,
  pg_catalog.count(_postgrest_t) AS page_total,
  array[]::text[] AS header,
  coalesce(json_agg(_postgrest_t), '[]')::character varying AS body,
  nullif(current_setting('response.headers', true), '') AS response_headers,
  nullif(current_setting('response.status', true), '') AS response_status
FROM (SELECT * FROM pgrst_source) _postgrest_t;

 total_result_set | page_total | header |                                 body                                 | response_headers | response_status
------------------+------------+--------+----------------------------------------------------------------------+------------------+-----------------
                  |          2 | {}     | [{"client_id":2,"id":3,"name":"Newnew","clients":{"name":"Apple"}}, +|                  |
                  |            |        |  {"client_id":2,"id":4,"name":"Newnew","clients":{"name":"Apple"}}]  |                  |
(1 row)

One problem is that we would need a unique column(s) to join to. Similar to limited-updates-deletions, we could enforce that an order is present and we can join on the order columns.

Or we could also try to infer the PK and if it's not found we can require an order.

[steve-chavez]

Not sure if pg-safeupdate would limit us here. We'd have to test it.

Also related https://github.com/PostgREST/postgrest/issues/3013