proc.xo.go

package models

// Code generated by xo. DO NOT EDIT.

import (
	"context"
)

// Proc is a stored procedure.
type Proc struct {
	ProcID     string `json:"proc_id"`     // proc_id
	ProcName   string `json:"proc_name"`   // proc_name
	ProcType   string `json:"proc_type"`   // proc_type
	ReturnType string `json:"return_type"` // return_type
	ReturnName string `json:"return_name"` // return_name
	ProcDef    string `json:"proc_def"`    // proc_def
}

// PostgresProcs runs a custom query, returning results as Proc.
func PostgresProcs(ctx context.Context, db DB, schema string) ([]*Proc, error) {
	// query
	const sqlstr = `SELECT ` +
		`p.oid, ` + // ::varchar AS proc_id
		`p.proname, ` + // ::varchar AS proc_name
		`pp.proc_type, ` + // ::varchar AS proc_type
		`format_type(pp.return_type, NULL), ` + // ::varchar AS return_type
		`pp.return_name, ` + // ::varchar AS return_name
		`p.prosrc ` + // ::varchar AS proc_def
		`FROM pg_catalog.pg_proc p ` +
		`JOIN pg_catalog.pg_namespace n ON (p.pronamespace = n.oid) ` +
		`JOIN ( ` +
		`SELECT ` +
		`p.oid, ` +
		`(CASE WHEN EXISTS( ` +
		`SELECT ` +
		`column_name ` +
		`FROM information_schema.columns ` +
		`WHERE table_name = 'pg_proc' ` +
		`AND column_name = 'prokind' ` +
		`) ` +
		`THEN (CASE p.prokind ` +
		`WHEN 'p' THEN 'procedure' ` +
		`WHEN 'f' THEN 'function' ` +
		`END) ` +
		`ELSE '' ` +
		`END) AS proc_type, ` +
		`UNNEST(COALESCE(p.proallargtypes, ARRAY[p.prorettype])) AS return_type, ` +
		`UNNEST(CASE ` +
		`WHEN p.proargmodes IS NULL THEN ARRAY[''] ` +
		`ELSE p.proargnames ` +
		`END) AS return_name, ` +
		`UNNEST(COALESCE(p.proargmodes, ARRAY['o'])) AS param_type ` +
		`FROM pg_catalog.pg_proc p ` +
		`) AS pp ON p.oid = pp.oid ` +
		`WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype ` +
		`AND (p.proargtypes[0] IS NULL ` +
		`OR p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype) ` +
		`AND (pp.proc_type = 'function' ` +
		`OR pp.proc_type = 'procedure') ` +
		`AND pp.param_type = 'o' ` +
		`AND n.nspname = $1`
	// run
	logf(sqlstr, schema)
	rows, err := db.QueryContext(ctx, sqlstr, schema)
	if err != nil {
		return nil, logerror(err)
	}
	defer rows.Close()
	// load results
	var res []*Proc
	for rows.Next() {
		var p Proc
		// scan
		if err := rows.Scan(&p.ProcID, &p.ProcName, &p.ProcType, &p.ReturnType, &p.ReturnName, &p.ProcDef); err != nil {
			return nil, logerror(err)
		}
		res = append(res, &p)
	}
	if err := rows.Err(); err != nil {
		return nil, logerror(err)
	}
	return res, nil
}

// MysqlProcs runs a custom query, returning results as Proc.
func MysqlProcs(ctx context.Context, db DB, schema string) ([]*Proc, error) {
	// query
	const sqlstr = `SELECT ` +
		`r.routine_name AS proc_id, ` +
		`r.routine_name AS proc_name, ` +
		`LOWER(r.routine_type) AS proc_type, ` +
		`COALESCE(p.dtd_identifier, 'void') AS return_type, ` +
		`COALESCE(p.parameter_name, '') AS return_name, ` +
		`r.routine_definition AS proc_def ` +
		`FROM information_schema.routines r ` +
		`LEFT JOIN information_schema.parameters p ON p.specific_schema = r.routine_schema ` +
		`AND p.specific_name = r.routine_name ` +
		`AND (p.parameter_mode = 'OUT' OR p.ordinal_position = 0) ` +
		`WHERE r.routine_schema = ? ` +
		`ORDER BY r.routine_name, p.ordinal_position`
	// run
	logf(sqlstr, schema)
	rows, err := db.QueryContext(ctx, sqlstr, schema)
	if err != nil {
		return nil, logerror(err)
	}
	defer rows.Close()
	// load results
	var res []*Proc
	for rows.Next() {
		var p Proc
		// scan
		if err := rows.Scan(&p.ProcID, &p.ProcName, &p.ProcType, &p.ReturnType, &p.ReturnName, &p.ProcDef); err != nil {
			return nil, logerror(err)
		}
		res = append(res, &p)
	}
	if err := rows.Err(); err != nil {
		return nil, logerror(err)
	}
	return res, nil
}

// SqlserverProcs runs a custom query, returning results as Proc.
func SqlserverProcs(ctx context.Context, db DB, schema string) ([]*Proc, error) {
	// query
	const sqlstr = `SELECT ` +
		`STR(o.object_id) AS proc_id, ` +
		`o.name AS proc_name, ` +
		`(CASE o.type ` +
		`WHEN 'P' THEN 'procedure' ` +
		`WHEN 'FN' THEN 'function' ` +
		`END) AS proc_type, ` +
		`CASE ` +
		`WHEN p.object_id IS NOT NULL ` +
		`THEN TYPE_NAME(p.system_type_id)+IIF(p.precision > 0, '('+CAST(p.precision AS varchar)+IIF(p.scale > 0,','+CAST(p.scale AS varchar),'')+')', '') ` +
		`ELSE 'void' ` +
		`END AS return_type, ` +
		`CASE ` +
		`WHEN p.object_id IS NOT NULL AND p.name <> '' ` +
		`THEN SUBSTRING(p.name, 2, LEN(p.name)-1) ` +
		`ELSE '' ` +
		`END AS return_name, ` +
		`OBJECT_DEFINITION(o.object_id) AS proc_def ` +
		`FROM sys.objects o ` +
		`LEFT JOIN sys.parameters p ON o.object_id = p.object_id ` +
		`AND (p.object_id IS NULL OR p.is_output = 'true') ` +
		`WHERE o.type = 'P' ` +
		`OR o.type = 'FN' ` +
		`AND SCHEMA_NAME(o.schema_id) = @p1 ` +
		`ORDER BY o.object_id`
	// run
	logf(sqlstr, schema)
	rows, err := db.QueryContext(ctx, sqlstr, schema)
	if err != nil {
		return nil, logerror(err)
	}
	defer rows.Close()
	// load results
	var res []*Proc
	for rows.Next() {
		var p Proc
		// scan
		if err := rows.Scan(&p.ProcID, &p.ProcName, &p.ProcType, &p.ReturnType, &p.ReturnName, &p.ProcDef); err != nil {
			return nil, logerror(err)
		}
		res = append(res, &p)
	}
	if err := rows.Err(); err != nil {
		return nil, logerror(err)
	}
	return res, nil
}

// OracleProcs runs a custom query, returning results as Proc.
func OracleProcs(ctx context.Context, db DB, schema string) ([]*Proc, error) {
	// query
	const sqlstr = `SELECT ` +
		`CAST(o.object_id AS NVARCHAR2(255)) AS proc_id, ` +
		`LOWER(o.object_name) AS proc_name, ` +
		`LOWER(o.object_type) AS proc_type, ` +
		`LOWER(CASE ` +
		`WHEN a.data_type IS NULL THEN 'void' ` +
		`WHEN a.data_type = 'CHAR' THEN 'CHAR(' || a.data_length || ')' ` +
		`WHEN a.data_type = 'VARCHAR2' THEN 'VARCHAR2(' || a.data_length || ')' ` +
		`WHEN a.data_type = 'NUMBER' THEN 'NUMBER(' || NVL(a.data_precision, 0) || ',' || NVL(a.data_scale, 0) || ')' ` +
		`ELSE a.data_type END) AS return_type, ` +
		`LOWER(CASE ` +
		`WHEN a.argument_name IS NULL THEN '-' ` +
		`ELSE a.argument_name END) AS return_name, ` +
		`s.src AS proc_def ` +
		`FROM all_objects o ` +
		`LEFT JOIN sys.all_arguments a ON a.object_id = o.object_id ` +
		`AND a.in_out = 'OUT' ` +
		`JOIN ( ` +
		`SELECT ` +
		`s.owner, ` +
		`s.name, ` +
		`listagg(s.text) WITHIN GROUP (ORDER BY s.line) AS src ` +
		`FROM all_source s ` +
		`GROUP BY s.owner, s.name ` +
		`) s ON s.owner = o.owner ` +
		`AND s.name = o.object_name ` +
		`WHERE o.object_type IN ('FUNCTION','PROCEDURE') ` +
		`AND o.owner = UPPER(:1) ` +
		`ORDER BY o.object_id`
	// run
	logf(sqlstr, schema)
	rows, err := db.QueryContext(ctx, sqlstr, schema)
	if err != nil {
		return nil, logerror(err)
	}
	defer rows.Close()
	// load results
	var res []*Proc
	for rows.Next() {
		var p Proc
		// scan
		if err := rows.Scan(&p.ProcID, &p.ProcName, &p.ProcType, &p.ReturnType, &p.ReturnName, &p.ProcDef); err != nil {
			return nil, logerror(err)
		}
		res = append(res, &p)
	}
	if err := rows.Err(); err != nil {
		return nil, logerror(err)
	}
	return res, nil
}