konnosDashboard
triggers.ts49 lines · main
1import {
2 ident,
3 joinSqlFragments,
4 keyword,
5 literal,
6 safeSql,
7 type SafeSqlFragment,
8} from '../../../pg-format'
9import { PGTrigger, PGTriggerCreate } from '../../../pg-meta-triggers'
10
11// [Joshen] Writing this query within FE as the PATCH endpoint from pg-meta only supports updating
12// trigger name and enabled mode. So we'll delete and create the trigger, within a single transaction
13// Copying the SQL from https://github.com/supabase/postgres-meta/blob/master/src/lib/PostgresMetaTriggers.ts
14export function getDatabaseTriggerUpdateSQL({
15 originalTrigger,
16 updatedTrigger,
17}: {
18 originalTrigger: {
19 id: number
20 table_id: number
21 enabled_mode: 'ORIGIN' | 'REPLICA' | 'ALWAYS' | 'DISABLED'
22 name: string
23 table: string
24 schema: string
25 condition: string | null
26 orientation: 'ROW' | 'STATEMENT'
27 activation: 'BEFORE' | 'AFTER' | 'INSTEAD OF'
28 events: Array<string>
29 function_schema: string
30 function_name: string
31 function_args: Array<string>
32 }
33 updatedTrigger: Omit<PGTriggerCreate, 'events'> &
34 Pick<PGTrigger, 'enabled_mode'> & { events: Array<SafeSqlFragment> }
35}): SafeSqlFragment {
36 const { name, activation, events, schema, table, function_schema, function_name, function_args } =
37 updatedTrigger
38 const eventsList = joinSqlFragments(events, ' OR ')
39 const argsList =
40 function_args && function_args.length > 0
41 ? joinSqlFragments(function_args.map(literal), ',')
42 : safeSql``
43 return safeSql`BEGIN;
44DROP TRIGGER ${ident(originalTrigger.name)} ON ${ident(originalTrigger.schema)}.${ident(originalTrigger.table)};
45CREATE TRIGGER ${ident(name)} ${keyword(activation)} ${eventsList} ON ${ident(schema)}.${ident(table)}
46 FOR EACH ROW EXECUTE FUNCTION
47 ${ident(function_schema)}.${ident(function_name)}(${argsList});
48COMMIT;`
49}