文章介绍了PostgreSQL数据库通过psql工具使用hsql命令获取SQL命令语法帮助的功能。详细讲解了hsql命令的工作原理，包括从源码角度解析如何生成和使用这些帮助信息，以及hsql命令的执行流程。
目 录
学习参考书籍、网站或博文：
- 参考书籍：《PostgreSQL数据库内核分析》
- Lex & Yacc 点击前往
- Postgresql源码学习之词法和语法分析 点击前往
Postgres数据库之\h sql命令显示语法信息原理学习总结
功能简介
psql工具可以通过\h or \help [ command ]
给出指定SQL命令的语法帮助。如果没有指定command，则psql会列出可以显示语法帮助的所有命令。如果command是一个星号（*），则会显示所有SQL命令的语法帮助。
[postgres@local104:~/test/bin]$ ./psql -p 5832
psql (15.1)
Type "help" for help.
postgres=# \h alter database
Command: ALTER DATABASE
Description: change a database
Syntax:
ALTER DATABASE name [ [ WITH ] option [ ... ] ]
where option can be:
ALLOW_CONNECTIONS allowconn
CONNECTION LIMIT connlimit
IS_TEMPLATE istemplate
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER DATABASE name SET TABLESPACE new_tablespace
ALTER DATABASE name REFRESH COLLATION VERSION
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL
URL: https://www.postgresql.org/docs/15/sql-alterdatabase.html
postgres=# 2023-06-09 14:19:23.706 CST [28895] LOG: checkpoint starting: time
2023-06-09 14:19:23.916 CST [28895] LOG: checkpoint complete: wrote 5 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.206 s, sync=0.003 s, total=0.211 s; sync files=4, longest=0.003 s, average=0.001 s; distance=6 kB, estimate=6 kB
q
postgres-#
postgres-#
postgres-#
postgres-# ;
2023-06-09 14:22:14.475 CST [28904] ERROR: syntax error at or near "q" at character 1
2023-06-09 14:22:14.475 CST [28904] STATEMENT: q
;
ERROR: syntax error at or near "q"
LINE 1: q
^
postgres=#
postgres=#
postgres=#
postgres=# \h *
Command: ABORT
Description: abort the current transaction
Syntax:
ABORT [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
URL: https://www.postgresql.org/docs/15/sql-abort.html
Command: ALTER AGGREGATE
Description: change the definition of an aggregate function
Syntax:
ALTER AGGREGATE name ( aggregate_signature ) RENAME TO new_name
ALTER AGGREGATE name ( aggregate_signature )
OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER AGGREGATE name ( aggregate_signature ) SET SCHEMA new_schema
where aggregate_signature is:
* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]
URL: https://www.postgresql.org/docs/15/sql-alteraggregate.html
Command: ALTER COLLATION
Description: change the definition of a collation
Syntax:
ALTER COLLATION name REFRESH VERSION
ALTER COLLATION name RENAME TO new_name
ALTER COLLATION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER COLLATION name SET SCHEMA new_schema
URL: https://www.postgresql.org/docs/15/sql-altercollation.html
Command: ALTER CONVERSION
Description: change the definition of a conversion
Syntax:
ALTER CONVERSION name RENAME TO new_name
ALTER CONVERSION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER CONVERSION name SET SCHEMA new_schema
URL: https://www.postgresql.org/docs/15/sql-alterconversion.html
Command: ALTER DATABASE
Description: change a database
Syntax:
ALTER DATABASE name [ [ WITH ] option [ ... ] ]
postgres=# \h
Available help:
ABORT ALTER SEQUENCE CREATE AGGREGATE CREATE SUBSCRIPTION DROP EXTENSION DROP TEXT SEARCH PARSER RESET
ALTER AGGREGATE ALTER SERVER CREATE CAST CREATE TABLE DROP FOREIGN DATA WRAPPER DROP TEXT SEARCH TEMPLATE REVOKE
ALTER COLLATION ALTER STATISTICS CREATE COLLATION CREATE TABLE AS DROP FOREIGN TABLE DROP TRANSFORM ROLLBACK
ALTER CONVERSION ALTER SUBSCRIPTION CREATE CONVERSION CREATE TABLESPACE DROP FUNCTION DROP TRIGGER ROLLBACK PREPARED
ALTER DATABASE ALTER SYSTEM CREATE DATABASE CREATE TEXT SEARCH CONFIGURATION DROP GROUP DROP TYPE ROLLBACK TO SAVEPOINT
ALTER DEFAULT PRIVILEGES ALTER TABLE CREATE DOMAIN CREATE TEXT SEARCH DICTIONARY DROP INDEX DROP USER SAVEPOINT
ALTER DOMAIN ALTER TABLESPACE CREATE EVENT TRIGGER CREATE TEXT SEARCH PARSER DROP LANGUAGE DROP USER MAPPING SECURITY LABEL
ALTER EVENT TRIGGER ALTER TEXT SEARCH CONFIGURATION CREATE EXTENSION CREATE TEXT SEARCH TEMPLATE DROP MATERIALIZED VIEW DROP VIEW SELECT
ALTER EXTENSION ALTER TEXT SEARCH DICTIONARY CREATE FOREIGN DATA WRAPPER CREATE TRANSFORM DROP OPERATOR END SELECT INTO
ALTER FOREIGN DATA WRAPPER ALTER TEXT SEARCH PARSER CREATE FOREIGN TABLE CREATE TRIGGER DROP OPERATOR CLASS EXECUTE SET
ALTER FOREIGN TABLE ALTER TEXT SEARCH TEMPLATE CREATE FUNCTION CREATE TYPE DROP OPERATOR FAMILY EXPLAIN SET CONSTRAINTS
ALTER FUNCTION ALTER TRIGGER CREATE GROUP CREATE USER DROP OWNED FETCH SET ROLE
ALTER GROUP ALTER TYPE CREATE INDEX CREATE USER MAPPING DROP POLICY GRANT SET SESSION AUTHORIZATION
ALTER INDEX ALTER USER CREATE LANGUAGE CREATE VIEW DROP PROCEDURE IMPORT FOREIGN SCHEMA SET TRANSACTION
ALTER LANGUAGE ALTER USER MAPPING CREATE MATERIALIZED VIEW DEALLOCATE DROP PUBLICATION INSERT SHOW
ALTER LARGE OBJECT ALTER VIEW CREATE OPERATOR DECLARE DROP ROLE LISTEN START TRANSACTION
ALTER MATERIALIZED VIEW ANALYZE CREATE OPERATOR CLASS DELETE DROP ROUTINE LOAD TABLE
ALTER OPERATOR BEGIN CREATE OPERATOR FAMILY DISCARD DROP RULE LOCK TRUNCATE
ALTER OPERATOR CLASS CALL CREATE POLICY DO DROP SCHEMA MERGE UNLISTEN
ALTER OPERATOR FAMILY CHECKPOINT CREATE PROCEDURE DROP ACCESS METHOD DROP SEQUENCE MOVE UPDATE
ALTER POLICY CLOSE CREATE PUBLICATION DROP AGGREGATE DROP SERVER NOTIFY VACUUM
ALTER PROCEDURE CLUSTER CREATE ROLE DROP CAST DROP STATISTICS PREPARE VALUES
ALTER PUBLICATION COMMENT CREATE RULE DROP COLLATION DROP SUBSCRIPTION PREPARE TRANSACTION WITH
ALTER ROLE COMMIT CREATE SCHEMA DROP CONVERSION DROP TABLE REASSIGN OWNED
ALTER ROUTINE COMMIT PREPARED CREATE SEQUENCE DROP DATABASE DROP TABLESPACE REFRESH MATERIALIZED VIEW
ALTER RULE COPY CREATE SERVER DROP DOMAIN DROP TEXT SEARCH CONFIGURATION REINDEX
ALTER SCHEMA CREATE ACCESS METHOD CREATE STATISTICS DROP EVENT TRIGGER DROP TEXT SEARCH DICTIONARY RELEASE SAVEPOINT
postgres=# \h create table
Command: CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option ... ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
OF type_name [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
PARTITION OF parent_table [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ] { FOR VALUES partition_bound_spec | DEFAULT }
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr |
GENERATED ALWAYS AS ( generation_expr ) STORED |
postgres=#
原理
运行\h sql命令，便可显示指定SQL命令的语法帮助信息，下面介绍该功能源码上的处理流程
sql命令的语法帮助信息来自源码树目录postgres/doc/src/sgml/ref，该目录下是所有语法信息对应的sgml文件，一般新增语法需要增加对应的帮助信息，则需要为新增语法增加相应的xxx.sgml文件
源码相关文件
postgres/src/bin/psql/create_help.pl
postgres/src/bin/psql/sql_help.h
postgres/src/bin/psql/sql_help.c
postgres/src/bin/psql/help.c
create_help.pl脚本处理postgres/doc/src/sgml/ref目录下的子文件，生成sql_help.h和sql_help.c文件
相关Makefile如下：
create_help.pl脚本的内容
#! /usr/bin/perl
#################################################################
# create_help.pl -- converts SGML docs to internal psql help
#
# Copyright (c) 2000-2022, PostgreSQL Global Development Group
#
# src/bin/psql/create_help.pl
#################################################################
#
# This script automatically generates the help on SQL in psql from
# the SGML docs. So far the format of the docs was consistent
# enough that this worked, but this here is by no means an SGML
# parser.
#
# Call: perl create_help.pl docdir sql_help
# The name of the header file doesn't matter to this script, but it
# sure does matter to the rest of the source.
#
use strict;
use warnings;
my $docdir = $ARGV[0] or die "$0: missing required argument: docdir\n";
my $hfile = $ARGV[1] . '.h'
or die "$0: missing required argument: output file\n";
my $cfile = $ARGV[1] . '.c';
my $hfilebasename;
if ($hfile =~ m!.*/([^/]+)$!)
{
$hfilebasename = $1;
}
else
{
$hfilebasename = $hfile;
}
my $define = $hfilebasename;
$define =~ tr/a-z/A-Z/;
$define =~ s/\W/_/g;
opendir(DIR, $docdir)
or die "$0: could not open documentation source dir '$docdir': $!\n";
open(my $hfile_handle, '>', $hfile)
or die "$0: could not open output file '$hfile': $!\n";
open(my $cfile_handle, '>', $cfile)
or die "$0: could not open output file '$cfile': $!\n";
print $hfile_handle "/*
* *** Do not change this file by hand. It is automatically
* *** generated from the DocBook documentation.
*
* generated by src/bin/psql/create_help.pl
*
*/
#ifndef $define
#define $define
#include \"pqexpbuffer.h\"
struct _helpStruct
{
const char *cmd; /* the command name */
const char *help; /* the help associated with it */
const char *docbook_id; /* DocBook XML id (for generating URL) */
void (*syntaxfunc) (PQExpBuffer); /* function that prints the
* syntax associated with it */
int nl_count; /* number of newlines in syntax (for pager) */
};
extern const struct _helpStruct QL_HELP[];
";
print $cfile_handle "/*
* *** Do not change this file by hand. It is automatically
* *** generated from the DocBook documentation.
*
* generated by src/bin/psql/create_help.pl
*
*/
#define N_(x) (x) /* gettext noop */
#include \"postgres_fe.h\"
#include \"$hfile\"
";
my $maxlen = 0;
my %entries;
foreach my $file (sort readdir DIR)
{
my ($cmdid, @cmdnames, $cmddesc, $cmdsynopsis);
$file =~ /\.sgml$/ or next;
open(my $fh, '<', "$docdir/$file") or next;
my $filecontent = join('', <$fh>);
close $fh;
# Ignore files that are not for SQL language statements
$filecontent =~
m!<refmiscinfo>\s*SQL - Language Statements\s*</refmiscinfo>!i
or next;
$filecontent =~ m!<refentry id="([a-z-]+)">!
and $cmdid = $1;
# Collect multiple refnames
LOOP:
{
$filecontent =~ m!\G.*?<refname>\s*([a-z ]+?)\s*</refname>!cgis
and push @cmdnames, $1
and redo LOOP;
}
$filecontent =~ m!<refpurpose>\s*(.+?)\s*</refpurpose>!is
and $cmddesc = $1;
$filecontent =~ m!<synopsis>\s*(.+?)\s*</synopsis>!is
and $cmdsynopsis = $1;
if (@cmdnames && $cmddesc && $cmdid && $cmdsynopsis)
{
s/\"/\\"/g foreach @cmdnames;
$cmddesc =~ s/<[^>]+>//g;
$cmddesc =~ s/\s+/ /g;
$cmddesc =~ s/\"/\\"/g;
my @params = ();
my $nl_count = () = $cmdsynopsis =~ /\n/g;
$cmdsynopsis =~ s/%/%%/g;
while ($cmdsynopsis =~ m!<(\w+)[^>]*>(.+?)</\1[^>]*>!)
{
my $match = $2;
$match =~ s/<[^>]+>//g;
$match =~ s/%%/%/g;
push @params, $match;
$cmdsynopsis =~ s!<(\w+)[^>]*>.+?</\1[^>]*>!%s!;
}
$cmdsynopsis =~ s/\r?\n/\\n/g;
$cmdsynopsis =~ s/\"/\\"/g;
foreach my $cmdname (@cmdnames)
{
$entries{$cmdname} = {
cmdid => $cmdid,
cmddesc => $cmddesc,
cmdsynopsis => $cmdsynopsis,
params => \@params,
nl_count => $nl_count
};
$maxlen =
($maxlen >= length $cmdname) ? $maxlen : length $cmdname;
}
}
else
{
die "$0: parsing file '$file' failed (N='@cmdnames' D='$cmddesc')\n";
}
}
foreach (sort keys %entries)
{
my $prefix = "\t" x 5 . ' ';
my $id = $_;
$id =~ s/ /_/g;
my $synopsis = "\"$entries{$_}{cmdsynopsis}\"";
$synopsis =~ s/\\n/\\n"\n$prefix"/g;
my @args =
("buf", $synopsis, map("_(\"$_\")", @{ $entries{$_}{params} }));
print $cfile_handle "static void
sql_help_$id(PQExpBuffer buf)
{
\tappendPQExpBuffer(" . join(",\n$prefix", @args) . ");
}
";
}
print $cfile_handle "
const struct _helpStruct QL_HELP[] = {
";
foreach (sort keys %entries)
{
my $id = $_;
$id =~ s/ /_/g;
print $cfile_handle "\t{\"$_\",
\t\tN_(\"$entries{$_}{cmddesc}\"),
\t\t\"$entries{$_}{cmdid}\",
\t\tsql_help_$id,
\t$entries{$_}{nl_count}},
";
}
print $cfile_handle "
\t{NULL, NULL, NULL}\t\t\t/* End of list marker */
};
";
print $hfile_handle "
#define QL_HELP_COUNT "
. scalar(keys %entries) . " /* number of help items */
#define QL_MAX_CMD_LEN $maxlen /* largest strlen(cmd) */
#endif /* $define */
";
close $cfile_handle;
close $hfile_handle;
closedir DIR;
调用命令 perl create_help.pl postgres/doc/src/sgml/ref sql_help
脚本处理流程：
- 首先处理命令行参数，设置脚本中相关变量的值
docdir=postgres/doc/src/sgml/ref
hfile=sql_help.h
cfile=sql_help.c - 检查相关目录及文件是否能正常
open - 分别printf 部分内容到
sql_help.h和sql_help.c文件中
sql_help.c文件中首先写入红色框中的内容
foreach循环读取postgres/doc/src/sgml/ref目录下的sgml文件，并按照指定的格式，生成命令帮助信息的执行函数，以及QL_HELP[]数组
create_help.pl脚本在sql_help.h文件中定义了哈希键的数据结构
struct _helpStruct
{
const char *cmd; /* 命令名字 */
const char *help; /* 与之相关的帮助 */
const char *docbook_id; /* DocBook XML id（用于生成URL） */
void (*syntaxfunc)(UXSQLExpBuffer); /* 函数，用于打印与其关联的语法 */
int nl_count; /* 语法中的换行数 (for pager) */
};
根据每个sgml文件中匹配的字符串，分别设置$cmdid, @cmdnames, $cmddesc, $cmdsynopsis变量值，之后为entries设置各个键对应的value
params是语法中关键字之后匹配的变量名数组，如下面例子中的name、aggregate_signature 、new_name等
nl_count代表语法规则的行数
例如，当前读取的文件为alter_aggregate.sgml，则通过字符串匹配之后，如下变量值为：
cmdid：sql-alteraggregate
cmdname：ALTER AGGREGATE
cmddesc：change the definition of an aggregate function
cmdsynopsis：
ALTER AGGREGATE name ( aggregate_signature ) RENAME TO new_name
ALTER AGGREGATE name ( aggregate_signature )
OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER AGGREGATE name ( aggregate_signature ) SET SCHEMA new_schema
where aggregate_signature is:
* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]
下面的foreach操作用于生成单个语法规则对应的执行函数（sql_help_命令）
例如 ALTER_COLLATION对应的执行函数如下：
- 关闭文件及目录
\h sql 命令执行流程
入口函数helpSQL，该函数处理逻辑如下
550
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
