fix(triggers): list triggers on captitalized schema namespaces

avallete · soedirgo · commit fc6120a9bf28 · 2025-01-08T16:02:21.000+08:00
fix an issue where listing triggers declared over a schema with capitalized name
resulted into an error 3F000
diff --git a/src/lib/sql/triggers.sql b/src/lib/sql/triggers.sql
@@ -29,7 +29,7 @@ ON pg_t.tgrelid = pg_c.oid
 JOIN information_schema.triggers AS is_t
 ON is_t.trigger_name = pg_t.tgname
 AND pg_c.relname = is_t.event_object_table
-AND pg_c.relnamespace = is_t.event_object_schema::regnamespace
+AND pg_c.relnamespace = (quote_ident(is_t.event_object_schema))::regnamespace
 JOIN pg_proc AS pg_p
 ON pg_t.tgfoid = pg_p.oid
 JOIN pg_namespace AS pg_n
diff --git a/test/lib/triggers.ts b/test/lib/triggers.ts
@@ -264,3 +264,51 @@ create schema s2; create table s2.t(); create trigger tr before insert on s2.t e
 
   await pgMeta.query('drop schema s1 cascade; drop schema s2 cascade;')
 })
+
+test('triggers on capitalized schema and table names', async () => {
+  await pgMeta.query(`
+CREATE SCHEMA "MySchema";
+CREATE TABLE "MySchema"."MyTable" (
+   id SERIAL PRIMARY KEY,
+   name TEXT NOT NULL,
+   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+   updated_at TIMESTAMP
+);
+CREATE OR REPLACE FUNCTION "MySchema"."my_trigger_function"()
+RETURNS TRIGGER AS $$
+BEGIN
+   NEW.updated_at := CURRENT_TIMESTAMP;
+   RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER "my_trigger"
+BEFORE INSERT ON "MySchema"."MyTable"
+FOR EACH ROW
+EXECUTE FUNCTION "MySchema"."my_trigger_function"();
+`)
+
+  const res = await pgMeta.triggers.list()
+  const triggers = res.data?.map(({ id, table_id, ...trigger }) => trigger)
+  expect(triggers).toMatchInlineSnapshot(`
+    [
+      {
+        "activation": "BEFORE",
+        "condition": null,
+        "enabled_mode": "ORIGIN",
+        "events": [
+          "INSERT",
+        ],
+        "function_args": [],
+        "function_name": "my_trigger_function",
+        "function_schema": "MySchema",
+        "name": "my_trigger",
+        "orientation": "ROW",
+        "schema": "MySchema",
+        "table": "MyTable",
+      },
+    ]
+  `)
+
+  await pgMeta.query('drop schema "MySchema" cascade;')
+})
