sql: add getdatabaseencoding() function

[rafiss]

Postgres has a getdatabaseencoding() function that we should support.

[joshuabezaleel]

Are there any recommendations on where to start and references if someone is interested in this?
Thank you very much! 🙂

[avinashdhinwa]

Are there any recommendations on where to start and references if someone is interested in this?
Thank you very much! 🙂

@jordanlewis @rafiss

[rohany]

It looks like this information is stored in the database pg_database. You could get the information from there. To add a builtin function, you would need to add another function to the list in pkg/sql/sem/builtins/builtins.go. You could use the InternalExecutor object within the EvalContext to issue a query to that database.

Let me know if you have any questions!

[rafiss]

For reference, here are the encodings that Postgres uses:

 const pg_enc2gettext pg_enc2gettext_tbl[] =
 {
     {PG_SQL_ASCII, "US-ASCII"},
     {PG_UTF8, "UTF-8"},
     {PG_LATIN1, "LATIN1"},
     {PG_LATIN2, "LATIN2"},
     {PG_LATIN3, "LATIN3"},
     {PG_LATIN4, "LATIN4"},
     {PG_ISO_8859_5, "ISO-8859-5"},
     {PG_ISO_8859_6, "ISO_8859-6"},
     {PG_ISO_8859_7, "ISO-8859-7"},
     {PG_ISO_8859_8, "ISO-8859-8"},
     {PG_LATIN5, "LATIN5"},
     {PG_LATIN6, "LATIN6"},
     {PG_LATIN7, "LATIN7"},
     {PG_LATIN8, "LATIN8"},
     {PG_LATIN9, "LATIN-9"},
     {PG_LATIN10, "LATIN10"},
     {PG_KOI8R, "KOI8-R"},
     {PG_KOI8U, "KOI8-U"},
     {PG_WIN1250, "CP1250"},
     {PG_WIN1251, "CP1251"},
     {PG_WIN1252, "CP1252"},
     {PG_WIN1253, "CP1253"},
     {PG_WIN1254, "CP1254"},
     {PG_WIN1255, "CP1255"},
     {PG_WIN1256, "CP1256"},
     {PG_WIN1257, "CP1257"},
     {PG_WIN1258, "CP1258"},
     {PG_WIN866, "CP866"},
     {PG_WIN874, "CP874"},
     {PG_EUC_CN, "EUC-CN"},
     {PG_EUC_JP, "EUC-JP"},
     {PG_EUC_KR, "EUC-KR"},
     {PG_EUC_TW, "EUC-TW"},
     {PG_EUC_JIS_2004, "EUC-JP"},
     {PG_SJIS, "SHIFT-JIS"},
     {PG_BIG5, "BIG5"},
     {PG_GBK, "GBK"},
     {PG_UHC, "UHC"},
     {PG_GB18030, "GB18030"},
     {PG_JOHAB, "JOHAB"},
     {PG_SHIFT_JIS_2004, "SHIFT_JISX0213"},
     {0, NULL}
 };

This enum is referenced from https://github.com/postgres/postgres/blob/8e10405c745003c5c16acb2da847db9bed1a169e/src/backend/utils/mb/mbutils.c#L1048

[kul-amr]

Hey - I am new to OSS contribution. Can I work on this if no one else is attempting?

[rohany]

Sure, it doesn't look like whoever asked before is still working on this!

[tclass]

Hey, I thought I try to create a PR for this, but somehow I run into a stalling issue with my code and I can't find out why. Does anyone has a hint for me? When I call the function the query never ends and just stalls the whole system.

	"getdatabaseencoding": makeBuiltin(
		tree.FunctionProperties{Category: categorySystemInfo},
		tree.Overload{
			Types:      tree.ArgTypes{},
			ReturnType: tree.FixedReturnType(types.String),
			Fn: func(ctx *tree.EvalContext, args tree.Datums) (tree.Datum, error) {
				r, err := ctx.InternalExecutor.QueryRow(ctx.Ctx(),"getdatabaseencoding()",ctx.Txn,"SELECT getdatabaseencoding()")
				if err != nil || r == nil {
					return tree.NewDString("error"),err
				}
				return tree.NewDString("some result"), nil
			},
			Info: "Returns the encoding of the database",
		},
	),

[rohany]

You can't call getdatabaseencoding in the implementation of getdatabaseencoding -- its infinitely looping. You want to be getting rows from pg_catalog.pg_database.

[abhishek20123g]

hi, I have implemented ,

"getdatabaseencoding": makeBuiltin(
		tree.FunctionProperties{Category: categorySystemInfo},
		tree.Overload{
			Types:      tree.ArgTypes{},
			ReturnType: tree.FixedReturnType(types.String),
			Fn: func(ctx *tree.EvalContext, args tree.Datums) (tree.Datum, error) {
				r1, err1 := ctx.InternalExecutor.QueryRow(
					ctx.Ctx(), "getdatabaseencoding",
					ctx.Txn,
					"SELECT encoding FROM pg_database "+
						"WHERE datname = $1", ctx.SessionData.Database)
				encodingId := *(r1[0].(*tree.DInt))
				if err1 != nil {
					return tree.DNull, err1
				}

				// Using pg_encoding_to_char() to convert encodingId to encoding.
				r2, err2 := ctx.InternalExecutor.QueryRow(
					ctx.Ctx(), "getdatabaseencoding",
					ctx.Txn,
					"SELECT pg_encoding_to_char($1)", encodingId)
				if err2 != nil {
					return tree.DNull, err2
				}
				return r2[0], nil
			},
			Info: "Returns the current encoding used by database.",
		},
	),

thorw this i was able to provide query for database,
plz let me know if some improvement require.

I have tested it locally it working fine

[rohany]

If you think you have a working solution, please open a PR! We can review it there.

[abhishek20123g]

Thanks
I will open PR for review

[tclass]

@rohany oh man, can't believe I didn't get that it's calling itself (fiddled around for hours). Have to read up more about the architecture, sad that it's already solved now