认识语法帮助
当使用psql工具登录数据库时，\h command可以查看语法帮助。比如查看create database语法：
psql (17.6) Type "help" for help. ivorysql=# \h create database Command: CREATE DATABASE Description: create a new database Syntax:CREATE DATABASE name [ WITH ] [ OWNER [=] user_name ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ STRATEGY [=] strategy ] [ LOCALE [=] locale ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ BUILTIN_LOCALE [=] builtin_locale ] [ ICU_LOCALE [=] icu_locale ] [ ICU_RULES [=] icu_rules ] [ LOCALE_PROVIDER [=] locale_provider ] [ COLLATION_VERSION = collation_version ] [ TABLESPACE [=] tablespace_name ] [ ALLOW_CONNECTIONS [=] allowconn ] [ CONNECTION LIMIT [=] connlimit ] [ IS_TEMPLATE [=] istemplate ] [ OID [=] oid ] URL: https://www.postgresql.org/docs/17/sql-createdatabase.html ivorysql=#
这也算是个小技巧，不需要死记太多的sql语法，使用时直接查看就行。不仅打印出了对应sql的语法，还打印出了该语法对应的官方文档URL。
语法帮助原理分析
主要是@SOURCE_ROOT/src/bin/psql/help.c中 helpSQL函数实现查看语法的功能。
从Makefile看还得依赖sql_help.c, sql_help.h一起编译完成。而从meson.build看是make的时候才生成这两个文件的，并且是使用create_help.pl读取文档目录@SOURCE_ROOT@/doc/src/sgml/ref中的所有语法文档生成的。
sql_help = custom_target('psql_help',
output: ['sql_help.c', 'sql_help.h'],
depfile: 'sql_help.dep',
command: [
perl, files('create_help.pl'),
'--docdir', '@SOURCE_ROOT@/doc/src/sgml/ref',
'--depfile', '@DEPFILE@',
'--outdir', '@OUTDIR@',
'--basename', 'sql_help',
],
)
generated_sources += sql_help.to_list()
psql_sources += sql_help
可以看到doc/src/sgml/ref目录下确实包含了其他所有对象的create的sgml文件，并没有create_package的文件。
ls doc/src/sgml/ref/create* doc/src/sgml/ref/create_access_method.sgml doc/src/sgml/ref/create_extension.sgml doc/src/sgml/ref/create_operator.sgml doc/src/sgml/ref/create_server.sgml doc/src/sgml/ref/create_tsdictionary.sgml doc/src/sgml/ref/create_aggregate.sgml doc/src/sgml/ref/create_foreign_data_wrapper.sgml doc/src/sgml/ref/create_opfamily.sgml doc/src/sgml/ref/create_statistics.sgml doc/src/sgml/ref/create_tsparser.sgml doc/src/sgml/ref/create_cast.sgml doc/src/sgml/ref/create_foreign_table.sgml doc/src/sgml/ref/create_policy.sgml doc/src/sgml/ref/create_subscription.sgml doc/src/sgml/ref/create_tstemplate.sgml doc/src/sgml/ref/create_collation.sgml doc/src/sgml/ref/create_function.sgml doc/src/sgml/ref/create_procedure.sgml doc/src/sgml/ref/create_table_as.sgml doc/src/sgml/ref/create_type.sgml doc/src/sgml/ref/create_conversion.sgml doc/src/sgml/ref/create_group.sgml doc/src/sgml/ref/create_publication.sgml doc/src/sgml/ref/create_table.sgml doc/src/sgml/ref/create_user_mapping.sgml doc/src/sgml/ref/create_database.sgml doc/src/sgml/ref/create_index.sgml doc/src/sgml/ref/create_role.sgml doc/src/sgml/ref/create_tablespace.sgml doc/src/sgml/ref/create_user.sgml doc/src/sgml/ref/createdb.sgml doc/src/sgml/ref/create_language.sgml doc/src/sgml/ref/create_rule.sgml doc/src/sgml/ref/create_transform.sgml doc/src/sgml/ref/createuser.sgml doc/src/sgml/ref/create_domain.sgml doc/src/sgml/ref/create_materialized_view.sgml doc/src/sgml/ref/create_schema.sgml doc/src/sgml/ref/create_trigger.sgml doc/src/sgml/ref/create_view.sgml doc/src/sgml/ref/create_event_trigger.sgml doc/src/sgml/ref/create_opclass.sgml doc/src/sgml/ref/create_sequence.sgml doc/src/sgml/ref/create_tsconfig.sgml
增添create package语法帮助
仿照create_table.sgml或其他文件，生成create_package.sgml和create_package_body.sgml文件。
从create_help.pl可知： cmd取自sgml文件中 refname标签，help取自 synopsis标签，docbook_id取自 refentry id标签。
因此create_package.sgml为：
<!--
doc/src/sgml/ref/create_package.sgml
PostgreSQL documentation
-->
<refentry id="sql-createpackage">
<indexterm zone="sql-createpackage">
<primary>CREATE PACKAGE</primary>
</indexterm>
<refmeta>
<refentrytitle>CREATE PACKAGE</refentrytitle>
<manvolnum>7</manvolnum>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>CREATE PACKAGE</refname>
<refpurpose>define a new package</refpurpose>
</refnamediv>
<refsynopsisdiv>
<synopsis>
CREATE [ OR REPLACE ] PACKAGE [schema.] <replaceable class="parameter">package_name</replaceable> [invoker_rights_clause] [IS | AS]
item_list[, item_list ...]
END [<replaceable class="parameter">package_name</replaceable>]
invoker_rights_clause:
AUTHID [CURRENT_USER | DEFINER]
item_list:
[
function_declaration |
procedure_declaration |
type_definition |
cursor_declaration |
item_declaration
]
function_declaration:
FUNCTION <replaceable class="parameter">function_name</replaceable> [ (parameter_declaration[, ...]) ] RETURN datatype;
procedure_declaration:
PROCEDURE <replaceable class="parameter">procedure_name</replaceable> [ (parameter_declaration[, ...]) ]
type_definition:
record_type_definition |
ref_cursor_type_definition
cursor_declaration:
CURSOR <replaceable class="parameter">name</replaceable> [(cur_param_decl[, ...])] RETURN rowtype;
item_declaration:
cursor_declaration |
cursor_variable_declaration |
record_variable_declaration |
variable_declaration |
record_type_definition:
TYPE record_type IS RECORD ( variable_declaration [, variable_declaration]... ) ;
ref_cursor_type_definition:
TYPE type IS REF CURSOR [ RETURN type%ROWTYPE ];
cursor_variable_declaration:
curvar curtype;
record_variable_declaration:
recvar { record_type | rowtype_attribute | record_type%TYPE };
variable_declaration:
varname datatype [ [ NOT NULL ] := expr ]
parameter_declaration:
parameter_name [IN] datatype [[:= | DEFAULT] expr]
</synopsis>
</refsynopsisdiv>
create_package_body.sgml为：
<!-- doc/src/sgml/ref/create_package_body.sgml PostgreSQL documentation --> <refentry id="sql-createpackagebody"> <indexterm zone="sql-createpackagebody"> <primary>CREATE PACKAGE BODY</primary> </indexterm> <refmeta> <refentrytitle>CREATE PACKAGE BODY</refentrytitle> <manvolnum>7</manvolnum> <refmiscinfo>SQL - Language Statements</refmiscinfo> </refmeta> <refnamediv> <refname>CREATE PACKAGE BODY</refname> <refpurpose>define a new package body</refpurpose> </refnamediv> <refsynopsisdiv> <synopsis> CREATE [ OR REPLACE ] PACKAGE BODY [schema.] <replaceable class="parameter">package_name</replaceable> [IS | AS] [item_list[, item_list ...]] | item_list_2 [, item_list_2 ...] [initialize_section] END [ <replaceable class="parameter">package_name</replaceable> ]; initialize_section: BEGIN statement[, ...] item_list: [ function_declaration | procedure_declaration | type_definition | cursor_declaration | item_declaration ] item_list_2: [ function_declaration function_definition procedure_declaration procedure_definition cursor_definition ] function_definition: FUNCTION <replaceable class="parameter">function_name</replaceable> [ (parameter_declaration[, ...]) ] RETURN datatype [IS | AS] [declare_section] body; procedure_definition: PROCEDURE <replaceable class="parameter">procedure_name</replaceable> [ (parameter_declaration[, ...]) ] [IS | AS] [declare_section] body; cursor_definition: CURSOR <replaceable class="parameter">name</replaceable> [ (cur_param_decl[, ...]) ] RETURN rowtype IS select_statement; body: BEGIN statement[, ...] END [name]; statement: [<<LABEL>>] pl_statments[, ...]; </synopsis> </refsynopsisdiv>
当然还需对helpSQL函数做些修改，当查询的语法帮助为create package(body)时，展示IvorySQL对应的官方文档 URL。
for (i = 0; QL_HELP[i].cmd; i++)
{ if (pg_strncasecmp(topic, QL_HELP[i].cmd, len) == 0 || strcmp(topic, "*") == 0)
{
PQExpBufferData buffer; char *url;
initPQExpBuffer(&buffer);
QL_HELP[i].syntaxfunc(&buffer); if (pg_strncasecmp(QL_HELP[i].docbook_id, "sql-createpackage",17) == 0)
url = "https://www.ivorysql.org/en/docs/compatibillity_features/package \n"
"DETAIL: https://docs.ivorysql.org/en/ivorysql-doc/v4.6/v4.6/28"; else
url = psprintf("https://www.postgresql.org/docs/%s/%s.html", strstr(PG_VERSION, "devel") ? "devel" : PG_MAJORVERSION,
QL_HELP[i].docbook_id); /* # of newlines in format must match constant above! */
fprintf(output, _("Command: %s\n"
"Description: %s\n"
"Syntax:\n%s\n\n"
"URL: %s\n\n"),
QL_HELP[i].cmd,
_(QL_HELP[i].help),
buffer.data,
url); if (pg_strncasecmp(QL_HELP[i].docbook_id, "sql-createpackage",17)) free(url);
termPQExpBuffer(&buffer); /* If we have an exact match, exit. Fixes \h SELECT */
if (pg_strcasecmp(topic, QL_HELP[i].cmd) == 0) break;
}
}
重新编译安装psql工具后，已经可以查看create package的语法帮助了。
\h create package
psql (17.6)
Type "help" for help.
ivorysql=# \h create package
Command: CREATE PACKAGE
Description: define a new package
Syntax:CREATE [ OR REPLACE ] PACKAGE [schema.] package_name [invoker_rights_clause] [IS | AS]
item_list[, item_list ...]
END [package_name]
invoker_rights_clause:
AUTHID [CURRENT_USER | DEFINER]
item_list:
[
function_declaration |
procedure_declaration |
type_definition |
cursor_declaration |
item_declaration
]
function_declaration: FUNCTION function_name [ (parameter_declaration[, ...]) ] RETURN datatype;
procedure_declaration: PROCEDURE procedure_name [ (parameter_declaration[, ...]) ]
type_definition:
record_type_definition |
ref_cursor_type_definition
cursor_declaration: CURSOR name [(cur_param_decl[, ...])] RETURN rowtype;
item_declaration:
cursor_declaration |
cursor_variable_declaration |
record_variable_declaration |
variable_declaration |record_type_definition:
TYPE record_type IS RECORD ( variable_declaration [, variable_declaration]... ) ;
ref_cursor_type_definition:
TYPE type IS REF CURSOR [ RETURN type%ROWTYPE ];
cursor_variable_declaration:
curvar curtype;
record_variable_declaration:
recvar { record_type | rowtype_attribute | record_type%TYPE };
variable_declaration:
varname datatype [ [ NOT NULL ] := expr ]
parameter_declaration:
parameter_name [IN] datatype [[:= | DEFAULT] expr]
URL: https://www.ivorysql.org/en/docs/compatibillity_features/package
DETAIL: https://docs.ivorysql.org/en/ivorysql-doc/v4.6/v4.6/28ivorysql=#
\h create package body
psql (17.6) Type "help" for help. ivorysql=# \h create package body Command: CREATE PACKAGE BODY Description: define a new package body Syntax:CREATE [ OR REPLACE ] PACKAGE BODY [schema.] package_name [IS | AS] [item_list[, item_list ...]] | item_list_2 [, item_list_2 ...] [initialize_section]END [ package_name ]; initialize_section: BEGIN statement[, ...] item_list: [ function_declaration | procedure_declaration | type_definition | cursor_declaration | item_declaration ] item_list_2: [ function_declaration function_definition procedure_declaration procedure_definition cursor_definition ] function_definition: FUNCTION function_name [ (parameter_declaration[, ...]) ] RETURN datatype [IS | AS] [declare_section] body; procedure_definition: PROCEDURE procedure_name [ (parameter_declaration[, ...]) ] [IS | AS] [declare_section] body; cursor_definition: CURSOR name [ (cur_param_decl[, ...]) ] RETURN rowtype IS select_statement; body: BEGIN statement[, ...] END [name]; statement: [<<LABEL>>] pl_statments[, ...]; URL: https://www.ivorysql.org/en/docs/compatibillity_features/package DETAIL: https://docs.ivorysql.org/en/ivorysql-doc/v4.6/v4.6/28ivorysql=#
小结
psql的语法帮助文档挺好用的，本次给IvorySQL-4.6适配create package(body)语法帮助文档算是比较系统的了解了下语法帮助文档的原理。
引用：
[1] helpSQL: https://github.com/IvorySQL/IvorySQL/blob/IvorySQL_4.6/src/bin/psql/help.c#L575
[2] URL: https://www.ivorysql.org/en/docs/Compatibillity_Features/package
