TriggerList.utils.ts49 lines · main

1	import { ident, joinSqlFragments, keyword, safeSql, type SafeSqlFragment } from '@supabase/pg-meta'
2
3	import type { DatabaseTriggersData } from '@/data/database-triggers/database-triggers-query'
4
5	export type PostgresTrigger = Omit<
6	DatabaseTriggersData[number],
7	'function_args' | 'condition' | 'events'
8	> & {
9	function_args: SafeSqlFragment[]
10	condition: SafeSqlFragment | null
11	events: SafeSqlFragment[]
12	}
13
14	export const generateTriggerCreateSQL = (trigger: PostgresTrigger): SafeSqlFragment => {
15	const events = joinSqlFragments(trigger.events, ' OR ')
16	const args =
17	trigger.function_args.length > 0
18	? safeSql`(${joinSqlFragments(trigger.function_args, ', ')})`
19	: safeSql`()`
20
21	// Note: CREATE OR REPLACE is not supported for triggers
22	// We need to drop the existing trigger first if we want to replace it
23	let sql = safeSql`
24	DROP TRIGGER IF EXISTS ${ident(trigger.name)} ON ${ident(trigger.schema)}.${ident(trigger.table)};
25
26	CREATE TRIGGER ${ident(trigger.name)}
27	${keyword(trigger.activation)} ${events}
28	ON ${ident(trigger.schema)}.${ident(trigger.table)}
29	FOR EACH ${keyword(trigger.orientation)}
30	`
31
32	if (trigger.condition) {
33	sql = safeSql`${sql} WHEN (${trigger.condition})\n`
34	}
35
36	sql = safeSql`${sql} EXECUTE FUNCTION ${ident(trigger.function_schema)}.${ident(trigger.function_name)}${args};`
37
38	return sql
39	}
40
41	export const getDatabaseFunctionsHref = (
42	projectRef: string | null | undefined,
43	schema: string | null | undefined,
44	name: string | null | undefined
45	): string => {
46	return `/project/${projectRef ?? ''}/database/functions?search=${encodeURIComponent(
47	name ?? ''
48	)}&schema=${encodeURIComponent(schema ?? '')}`
49	}