8000 feat(/functions): adds POST /functions, DELETE /functions and PATCH /functions by i-pip · Pull Request #118 · supabase/postgres-meta · GitHub
[go: up one dir, main page]
Skip to content

feat(/functions): adds POST /functions, DELETE /functions and PATCH /functions #118

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 9 commits into from
Jul 16, 2021
Merged

feat(/functions): adds POST /functions, DELETE /functions and PATCH /functions #118

Show file tree
Hide file tree
Changes from 8 commits
Commits
Show all changes
9 commits
Select commit Hold shift + click to select a range
2f6ab96
feat(/functions): adds POST /functions and DELETE /functions
i-pip Jun 19, 2021
1aca9a9
feat(/functions): implements PATCH /functions
i-pip Jun 20, 2021
13f421f
Merge branch 'develop' of github.com:supabase/postgres-meta into develop
i-pip Jul 7, 2021
13799a8
Merge branch 'develop' into feat/add_post_delete_on_functions
i-pip Jul 7, 2021
2caf733
feat(/functions): implements PATCH /functions for function schema and…
i-pip Jul 7, 2021
c2630e7
refactor(/functions): implementing changes requested by @w3b6x9
i-pip Jul 14, 2021
ea0a89e
test(test/integration): moved "DROP SCHEMA" to after hook
i-pip Jul 15, 2021
96806a0
test(integration/test): replaced deprecated `assert.equal` with `asse…
i-pip Jul 15, 2021
e7a975b
refactor(/functions): a few more improvements & clean up after code r…
i-pip Jul 15, 2021
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
100 changes: 96 additions & 4 deletions src/lib/PostgresMetaFunctions.ts
View file
Open in desktop
10000
Original file line number Diff line number Diff line change
@@ -1,4 +1,4 @@
import { literal } from 'pg-format'
import { ident, literal } from 'pg-format'
import { DEFAULT_SYSTEM_SCHEMAS } from './constants'
import { functionsSql } from './sql'
import { PostgresMetaResult, PostgresFunction } from './types'
Expand All @@ -22,13 +22,21 @@ export default class PostgresMetaFunctions {
}

async retrieve({ id }: { id: number }): Promise<PostgresMetaResult<PostgresFunction>>
async retrieve({ name }: { name: string }): Promise<PostgresMetaResult<PostgresFunction>>
async retrieve({
name,
schema,
}: {
name: string
schema: string
}): Promise<PostgresMetaResult<PostgresFunction>>
async retrieve({
id,
name,
schema = 'public',
}: {
id?: number
name?: string
schema?: string
}): Promise<PostgresMetaResult<PostgresFunction>> {
if (id) {
const sql = `${functionsSql} WHERE p.oid = ${literal(id)};`
Expand All @@ -41,14 +49,16 @@ export default class PostgresMetaFunctions {
return { data: data[0], error }
}
} else if (name) {
const sql = `${functionsSql} WHERE p.proname = ${literal(name)};`
const sql = `${functionsSql} WHERE p.proname = ${literal(name)} AND n.nspname = ${literal(
schema
)};`
const { data, error } = await this.query(sql)
if (error) {
return { data, error }
} else if (data.length === 0) {
return {
data: null,
error: { message: `Cannot find a function named ${name}` },
error: { message: `Cannot find a function named ${name} in schema ${schema}` },
}
} else {
return { data: data[0], error }
Expand All @@ -57,4 +67,86 @@ export default class PostgresMetaFunctions {
return { data: null, error: { message: 'Invalid parameters on function retrieve' } }
}
}

async create({
name,
schema = 'public',
params,
definition,
rettype = 'void',
language = 'sql',
}: {
name: string
schema?: string
params?: string[]
definition: string
rettype?: string
language?: string
}): Promise<PostgresMetaResult<PostgresFunction>> {
const sql = `
CREATE OR REPLACE FUNCTION ${ident(schema)}.${ident(name)}
${params && params.length ? `(${params.join(',')})` : '()'}
RETURNS ${rettype}
AS ${literal(definition)}
LANGUAGE ${language}
RETURNS NULL ON NULL INPUT;
`
const { error } = await this.query(sql)
if (error) {
return { data: null, error }
}
return await this.retrieve({ name, schema })
}

async update(
id: number,
{
name,
schema = 'public',
}: {
name: string
schema?: string
}
): Promise<PostgresMetaResult<PostgresFunction>> {
const { data: old, error: retrieveError } = await this.retrieve({ id })
if (retrieveError) {
return { data: null, error: retrieveError }
}

let alter = `ALTER FUNCTION ${ident(old!.name)}`
const nameSql =
name === undefined || name == old!.name ? '' : `${alter} RENAME TO ${ident(name)};`

alter = `ALTER FUNCTION ${ident(name)}`
const schemaSql =
schema === undefined || schema == old!.schema ? '' : `${alter} SET SCHEMA ${ident(schema)};`

const sql = `BEGIN;${nameSql} ${schemaSql} COMMIT;`

const { error } = await this.query(sql)
if (error) {
return { data: null, error }
}
return await this.retrieve({ id })
}

async remove(
id: number,
{ cascade = false } = {}
): Promise<PostgresMetaResult<PostgresFunction>> {
const { data: func, error } = await this.retrieve({ id })
if (error) {
return { data: null, error }
}
const sql = `DROP FUNCTION ${ident(func!.schema)}.${ident(func!.name)}
${func!.argument_types ? `(${func!.argument_types})` : '()'}
${cascade ? 'CASCADE' : 'RESTRICT'};`
{
const { error } = await this.query(sql)
if (error) {
return { data: null, error }
}
}
return { data: func!, error: null }
}
}
60 changes: 60 additions & 0 deletions src/server/routes/functions.ts
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -43,4 +43,64 @@ export default async (fastify: FastifyInstance) => {

return data
})

fastify.post<{
Headers: { pg: string }
Body: any
}>('/', async (request, reply) => {
const connectionString = request.headers.pg

const pgMeta = new PostgresMeta({ connectionString, max: 1 })
const { data, error } = await pgMeta.functions.create(request.body)
await pgMeta.end()
if (error) {
request.log.error(JSON.stringify({ error, req: request.body }))
reply.code(400)
return { error: error.message }
}
return data
})

fastify.patch<{
Headers: { pg: string }
Params: {
id: string
}
Body: any
}>('/:id(\\d+)', async (request, reply) => {
const connectionString = request.headers.pg
const id = Number(request.params.id)

const pgMeta = new PostgresMeta({ connectionString, max: 1 })
const { data, error } = await pgMeta.functions.update(id, request.body)
await pgMeta.end()
if (error) {
request.log.error(JSON.stringify({ error, req: request.body }))
reply.code(400)
if (error.message.startsWith('Cannot find')) reply.code(404)
return { error: error.message }
}
return data
})

fastify.delete<{
Headers: { pg: string }
Params: {
id: string
}
}>('/:id(\\d+)', async (request, reply) => {
const connectionString = request.headers.pg
const id = Number(request.params.id)

const pgMeta = new PostgresMeta({ connectionString, max: 1 })
const { data, error } = await pgMeta.functions.remove(id)
await pgMeta.end()
if (error) {
request.log.error(JSON.stringify({ error, req: request.body }))
reply.code(400)
if (error.message.startsWith('Cannot find')) reply.code(404)
return { error: error.message }
}
return data
})
}
Loading
0