sql: pg_function_is_visible is not correct for UDFs

**Describe the problem**

This builtin was added in 2019. The assumptions from back then no longer hold. See:

https://github.com/cockroachdb/cockroach/blob/1ca61aee52cd32c6cada5de981058d0026cfa7eb/pkg/sql/sem/builtins/pg_builtins.go#L1178-L1195

**Expected behavior**
Match the Postgres implementation, and take into account the schemas on the search path. Maybe it also takes into account privileges?

Also, the current implementation is needlessly slow. I hope we can improve the speed of it too.

**Environment:**
 - CockroachDB v22.2.0-beta

cc @chengxiong-ruan @mgartner 


Jira issue: CRDB-20297

Epic CRDB-19147

	// pg_function_is_visible returns true if the input oid corresponds to a
	// builtin function that is part of the databases on the search path.
	// CockroachDB doesn't have a concept of namespaced functions, so this is
	// always true if the builtin exists at all, and NULL otherwise.
	// https://www.postgresql.org/docs/9.6/static/functions-info.html
	"pg_function_is_visible": makeBuiltin(defProps(),
	tree.Overload{
	Types: tree.ArgTypes{{"oid", types.Oid}},
	ReturnType: tree.FixedReturnType(types.Bool),
	Fn: func(ctx *eval.Context, args tree.Datums) (tree.Datum, error) {
	oid := tree.MustBeDOid(args[0])
	t, err := ctx.Planner.QueryRowEx(
	ctx.Ctx(), "pg_function_is_visible",
	sessiondata.NoSessionDataOverride,
	"SELECT * from pg_proc WHERE oid=$1 LIMIT 1", oid.Oid)
	if err != nil {
	return nil, err
	}

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sql: pg_function_is_visible is not correct for UDFs #89546

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

sql: pg_function_is_visible is not correct for UDFs #89546

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions