8000 Merge pull request #131 from supabase/feat/function-options · DavraYoung/postgres-meta@b139fd6 · GitHub
[go: up one dir, main page]
Skip to content

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit b139fd6

Browse files
w3b6x9w3b6x9
authored
Merge pull request supabase#131 from supabase/feat/function-options
feat: add additional function create options
2 parents 7cdfd93 + 03ac6d0 commit b139fd6

File tree

4 files changed

+56
-2
lines changed

4 files changed

+56
-2
lines changed

‎src/lib/PostgresMetaFunctions.ts

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -82,21 +82,37 @@ export default class PostgresMetaFunctions {
8282
definition,
8383
rettype = 'void',
8484
language = 'sql',
85+
behavior = 'VOLATILE',
86+
security_definer = false,
87+
config_params,
8588
}: {
8689
name: string
8790
schema?: string
8891
args?: string[]
8992
definition: string
9093
rettype?: string
9194
language?: string
95+
behavior?: 'IMMUTABLE' | 'STABLE' | 'VOLATILE'
96+
security_definer?: boolean
97+
config_params: { [key: string]: string[] }
9298
}): Promise<PostgresMetaResult<PostgresFunction>> {
9399
const sql = `
94100
CREATE FUNCTION ${ident(schema)}.${ident(name)}(${args.join(', ')})
95101
RETURNS ${rettype}
96102
AS ${literal(definition)}
97103
LANGUAGE ${language}
104+
${behavior}
105+
${security_definer ? 'SECURITY DEFINER' : 'SECURITY INVOKER'}
106+
${Object.entries(config_params)
107+
.map(
108+
([param, values]) =>
109+
`SET ${param} ${
110+
values[0] === 'FROM CURRENT' ? 'FROM CURRENT' : 'TO ' + values.map(ident).join(',')
111+
}`
112+
)
113+
.join('\n')}
98114
RETURNS NULL ON NULL INPUT;
99-
`
115+
`
100116
const { error } = await this.query(sql)
101117
if (error) {
102118
return { data: null, error }

‎src/lib/sql/functions.sql

Lines changed: 26 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,9 +8,34 @@ SELECT
88
ELSE pg_get_functiondef(p.oid)
99
END AS definition,
1010
pg_get_function_arguments(p.oid) AS argument_types,
11-
t.typname AS return_type
11+
t.typname AS return_type,
12+
CASE
13+
WHEN p.provolatile = 'i' THEN 'IMMUTABLE'
14+
WHEN p.provolatile = 's' THEN 'STABLE'
15+
WHEN p.provolatile = 'v' THEN 'VOLATILE'
16+
END AS behavior,
17+
p.prosecdef AS security_definer,
18+
JSON_OBJECT_AGG(p_config.param, p_config.values)
19+
FILTER (WHERE p_config.param IS NOT NULL) AS config_params
1220
FROM
1321
pg_proc p
1422
LEFT JOIN pg_namespace n ON p.pronamespace = n.oid
1523
LEFT JOIN pg_language l ON p.prolang = l.oid
1624
LEFT JOIN pg_type t ON t.oid = p.prorettype
25+
LEFT JOIN (
26+
SELECT
27+
oid as id,
28+
(string_to_array(unnest(proconfig), '='))[1] AS param,
29+
string_to_array((string_to_array(unnest(proconfig), '='))[2], ', ') AS values
30+
FROM
31+
pg_proc
32+
) p_config ON p_config.id = p.oid
33+
GROUP BY
34+
p.oid,
35+
n.nspname,
36+
p.proname,
37+
l.lanname,
38+
p.prosrc,
39+
t.typname,
40+
p.provolatile,
41+
p.prosecdef

‎src/lib/types.ts

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,13 @@ const postgresFunctionSchema = Type.Object({
7878
definition: Type.String(),
7979
argument_types: Type.String(),
8080
return_type: Type.String(),
81+
behavior: Type.Union([
82+
Type.Literal('IMMUTABLE'),
83+
Type.Literal('STABLE'),
84+
Type.Literal('VOLATILE'),
85+
]),
86+
security_definer: Type.Boolean(),
87+
config_params: Type.Union([Type.Dict(Type.Array(Type.String())), Type.Null()]),
8188
})
8289
export type PostgresFunction = Static<typeof postgresFunctionSchema>
8390

‎test/integration/index.spec.js

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -165,6 +165,9 @@ describe('/functions', () => {
165165
definition: 'select $1 + $2',
166166
rettype: 'integer',
167167
language: 'sql',
168+
behavior: 'STABLE',
169+
security_definer: true,
170+
config_params: { search_path: ['hooks', 'auth'], role: ['postgres'] },
168171
}
169172
before(async () => {
170173
await axios.post(`${URL}/query`, {
@@ -212,6 +215,9 @@ describe('/functions', () => {
212215
assert.strictEqual(newFunc.schema, 'public')
213216
assert.strictEqual(newFunc.language, 'sql')
214217
assert.strictEqual(newFunc.return_type, 'int4')
218+
assert.strictEqual(newFunc.behavior, 'STABLE')
219+
assert.strictEqual(newFunc.security_definer, true)
220+
assert.deepStrictEqual(newFunc.config_params, { search_path: ['hooks', 'auth'], role: ['postgres'] })
215221
func.id = newFunc.id
216222
})
217223
it('PATCH', async () => {

0 commit comments

Comments
 (0)
0