Thread: Bug of psql meta-command \sf & \sv
Hi there,
The psql meta-commands \sf and \sv have a minor bug, for example:
postgres=# CREATE PROCEDURE test () AS $$ BEGIN NULL; END; $$ LANGUAGE plpgsql; -- create a procedure
postgres=# \sf test
CREATE OR REPLACE PROCEDURE public.test()
LANGUAGE plpgsql
AS $procedure$ BEGIN NULL; END; $procedure$
We can use \sf to check the souce of test, but if we use:
postgres=# \sf test;
2023-09-27 16:51:58.632 CST [3460153] ERROR: function "test;" does not exist at character 8
2023-09-27 16:51:58.632 CST [3460153] STATEMENT: SELECT 'test;'::pg_catalog.regproc::pg_catalog.oid
ERROR: function "test;" does not exist
The \sf feedback an error.
And the same error also exists on \sv command:
postgres=# CREATE VIEW v_test AS SELECT * FROM pg_class;
postgres=# \sv v_test
CREATE OR REPLACE VIEW public.v_test AS
SELECT pg_class.oid,
pg_class.relname,
pg_class.relnamespace,
pg_class.reltype,
pg_class.reloftype,
pg_class.relowner,
pg_class.relam,
pg_class.relfilenode,
pg_class.reltablespace,
pg_class.relpages,
pg_class.reltuples,
pg_class.relallvisible,
pg_class.reltoastrelid,
pg_class.relhasindex,
pg_class.relisshared,
pg_class.relpersistence,
pg_class.relkind,
pg_class.relnatts,
pg_class.relchecks,
pg_class.relhasrules,
pg_class.relhastriggers,
pg_class.relhassubclass,
pg_class.relrowsecurity,
pg_class.relforcerowsecurity,
pg_class.relispopulated,
pg_class.relreplident,
pg_class.relispartition,
pg_class.relrewrite,
pg_class.relfrozenxid,
pg_class.relminmxid,
pg_class.relacl,
pg_class.reloptions,
pg_class.relpartbound
FROM pg_class
postgres=# \sv v_test;
2023-09-27 16:56:10.086 CST [3460153] ERROR: relation "v_test;" does not exist at character 8
2023-09-27 16:56:10.086 CST [3460153] STATEMENT: SELECT 'v_test;'::pg_catalog.regclass::pg_catalog.oid
ERROR: relation "v_test;" does not exist
章晨曦 Jet C.X. ZHANG
18657181679
易景科技 http://www.halodbtech.com
浙江省杭州市滨江区长河街道建业路511号华创大厦5层505
Attachment
> On 27 Sep 2023, at 11:06, Jet Zhang <jet.cx.zhang@hotmail.com> wrote: > > Hi there, > > The psql meta-commands \sf and \sv have a minor bug, for example: > > postgres=# CREATE PROCEDURE test () AS $$ BEGIN NULL; END; $$ LANGUAGE plpgsql; -- create a procedure > postgres=# \sf test > CREATE OR REPLACE PROCEDURE public.test() > LANGUAGE plpgsql > AS $procedure$ BEGIN NULL; END; $procedure$ > > We can use \sf to check the souce of test, but if we use: > postgres=# \sf test; > 2023-09-27 16:51:58.632 CST [3460153] ERROR: function "test;" does not exist at character 8 > 2023-09-27 16:51:58.632 CST [3460153] STATEMENT: SELECT 'test;'::pg_catalog.regproc::pg_catalog.oid > ERROR: function "test;" does not exist > > The \sf feedback an error. This is not a bug, "test;" is a valid name which is distinct from test. Semi-colon is not a meta-command terminator. postgres=# create function "test;"() returns text as $$ begin null; end; $$ language plpgsql; CREATE FUNCTION postgres=# \sf test ERROR: function "test" does not exist postgres=# \sf test; CREATE OR REPLACE FUNCTION public."test;"() RETURNS text LANGUAGE plpgsql AS $function$ begin null; end; $function$ postgres=# -- Daniel Gustafsson
I don’t think not a bug. As you said Semi-colon is not a meta-command terminator, but why the other meta-commands
Exp. \dt able to work with Semi-colon?
postgres=# \dt pg_class
List of relations
Schema | Name | Type | Owner
------------+----------+-------+-------
pg_catalog | pg_class | table | jet
(1 row)
postgres=# \dt pg_class;
List of relations
Schema | Name | Type | Owner
------------+----------+-------+-------
pg_catalog | pg_class | table | jet
(1 row)
章晨曦 Jet C.X. ZHANG
18657181679
易景科技 http://www.halodbtech.com
浙江省杭州市滨江区长河街道建业路511号华创大厦5层505
Sent: Wednesday, September 27, 2023 5:13:32 PM
To: Jet Zhang <jet.cx.zhang@hotmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Bug of psql meta-command \sf & \sv
>
> Hi there,
>
> The psql meta-commands \sf and \sv have a minor bug, for example:
>
> postgres=# CREATE PROCEDURE test () AS $$ BEGIN NULL; END; $$ LANGUAGE plpgsql; -- create a procedure
> postgres=# \sf test
> CREATE OR REPLACE PROCEDURE public.test()
> LANGUAGE plpgsql
> AS $procedure$ BEGIN NULL; END; $procedure$
>
> We can use \sf to check the souce of test, but if we use:
> postgres=# \sf test;
> 2023-09-27 16:51:58.632 CST [3460153] ERROR: function "test;" does not exist at character 8
> 2023-09-27 16:51:58.632 CST [3460153] STATEMENT: SELECT 'test;'::pg_catalog.regproc::pg_catalog.oid
> ERROR: function "test;" does not exist
>
> The \sf feedback an error.
This is not a bug, "test;" is a valid name which is distinct from test.
Semi-colon is not a meta-command terminator.
postgres=# create function "test;"() returns text as $$ begin null; end; $$ language plpgsql;
CREATE FUNCTION
postgres=# \sf test
ERROR: function "test" does not exist
postgres=# \sf test;
CREATE OR REPLACE FUNCTION public."test;"()
RETURNS text
LANGUAGE plpgsql
AS $function$ begin null; end; $function$
postgres=#
--
Daniel Gustafsson
Attachment
On Wed, 27 Sep 2023 at 17:25, Jet Zhang <jet.cx.zhang@hotmail.com> wrote: > I don’t think not a bug. As you said Semi-colon is not a meta-command terminator, but why the other meta-commands > Exp. \dt able to work with Semi-colon? > > postgres=# \dt pg_class > List of relations > Schema | Name | Type | Owner > ------------+----------+-------+------- > pg_catalog | pg_class | table | jet > (1 row) > > postgres=# \dt pg_class; > List of relations > Schema | Name | Type | Owner > ------------+----------+-------+------- > pg_catalog | pg_class | table | jet > (1 row) > > The \sf use the whole line as its input, it can accept more complex input, for example: postgres=# \sf test (int) CREATE OR REPLACE PROCEDURE public.test(IN id integer) LANGUAGE plpgsql AS $procedure$ BEGIN NULL; END; $procedure$ postgres=# \sf test (int, text) CREATE OR REPLACE PROCEDURE public.test(IN id integer, IN info text) LANGUAGE plpgsql AS $procedure$ BEGIN NULL; END; $procedure$ Same as \ef. OTOH, \ev and \sv use same function as \ef and \sf, See exec_command_ef_ev() and exec_command_sf_sv() in src/bin/psql/common.c file. static backslashResult exec_command_sf_sv(PsqlScanState scan_state, bool active_branch, const char *cmd, bool is_func) { backslashResult status = PSQL_CMD_SKIP_LINE; if (active_branch) { bool show_linenumbers = (strchr(cmd, '+') != NULL); PQExpBuffer buf; char *obj_desc; Oid obj_oid = InvalidOid; EditableObjectType eot = is_func ? EditableFunction : EditableView; buf = createPQExpBuffer(); obj_desc = psql_scan_slash_option(scan_state, OT_WHOLE_LINE, NULL, true); ^ -- use the whole line Maybe we can split \sf and \sv, however, I'm not sure it worth. -- Regrads, Japin Li ChengDu WenWu Information Technology Co., Ltd.
Yes, you’re right. Maybe we can spend some effort to strength the function psgl_scan_slash_option.
章晨曦 Jet C.X. ZHANG
18657181679
易景科技 http://www.halodbtech.com
浙江省杭州市滨江区长河街道建业路511号华创大厦5层505
From: Japin Li
Sent: 2023年9月27日 18:33
To: Jet Zhang
Cc: Daniel Gustafsson; pgsql-bugs@lists.postgresql.org
Subject: Re: Bug of psql meta-command \sf & \sv
On Wed, 27 Sep 2023 at 17:25, Jet Zhang <jet.cx.zhang@hotmail.com> wrote:
> I don’t think not a bug. As you said Semi-colon is not a meta-command terminator, but why the other meta-commands
> Exp. \dt able to work with Semi-colon?
>
> postgres=# \dt pg_class
> List of relations
> Schema | Name | Type | Owner
> ------------+----------+-------+-------
> pg_catalog | pg_class | table | jet
> (1 row)
>
> postgres=# \dt pg_class;
> List of relations
> Schema | Name | Type | Owner
> ------------+----------+-------+-------
> pg_catalog | pg_class | table | jet
> (1 row)
>
>
The \sf use the whole line as its input, it can accept more complex input,
for example:
postgres=# \sf test (int)
CREATE OR REPLACE PROCEDURE public.test(IN id integer)
LANGUAGE plpgsql
AS $procedure$ BEGIN NULL; END; $procedure$
postgres=# \sf test (int, text)
CREATE OR REPLACE PROCEDURE public.test(IN id integer, IN info text)
LANGUAGE plpgsql
AS $procedure$ BEGIN NULL; END; $procedure$
Same as \ef.
OTOH, \ev and \sv use same function as \ef and \sf, See exec_command_ef_ev()
and exec_command_sf_sv() in src/bin/psql/common.c file.
static backslashResult
exec_command_sf_sv(PsqlScanState scan_state, bool active_branch,
const char *cmd, bool is_func)
{
backslashResult status = PSQL_CMD_SKIP_LINE;
if (active_branch)
{
bool show_linenumbers = (strchr(cmd, '+') != NULL);
PQExpBuffer buf;
char *obj_desc;
Oid obj_oid = InvalidOid;
EditableObjectType eot = is_func ? EditableFunction : EditableView;
buf = createPQExpBuffer();
obj_desc = psql_scan_slash_option(scan_state,
OT_WHOLE_LINE, NULL, true);
^ -- use the whole line
Maybe we can split \sf and \sv, however, I'm not sure it worth.
--
Regrads,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.
Attachment
>
> Hi there,
>
> The psql meta-commands \sf and \sv have a minor bug, for example:
>
> postgres=# CREATE PROCEDURE test () AS $$ BEGIN NULL; END; $$ LANGUAGE plpgsql; -- create a procedure
> postgres=# \sf test
> CREATE OR REPLACE PROCEDURE public.test()
> LANGUAGE plpgsql
> AS $procedure$ BEGIN NULL; END; $procedure$
>
> We can use \sf to check the souce of test, but if we use:
> postgres=# \sf test;
> 2023-09-27 16:51:58.632 CST [3460153] ERROR: function "test;" does not exist at character 8
> 2023-09-27 16:51:58.632 CST [3460153] STATEMENT: SELECT 'test;'::pg_catalog.regproc::pg_catalog.oid
> ERROR: function "test;" does not exist
>
> The \sf feedback an error.
This is not a bug, "test;" is a valid name which is distinct from test.
Semi-colon is not a meta-command terminator.
postgres=# create function "test;"() returns text as $$ begin null; end; $$ language plpgsql;
CREATE FUNCTION
postgres=# \sf test
ERROR: function "test" does not exist
postgres=# \sf test;
CREATE OR REPLACE FUNCTION public."test;"()
RETURNS text
LANGUAGE plpgsql
AS $function$ begin null; end; $function$
postgres=#
--
Daniel Gustafsson
On Wed, 27 Sep 2023 at 20:35, 咸🐟 <2437705447@qq.com> wrote: > https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS > I don't think this meets the documentation's definition of an identifier. Semicolons should not be used this wayhere.(Excuse me, I need to test the email function.) > In PostgreSQL, quoted identifiers can contain any character, except the character with code zero. For example: postgres=# CREATE table "<>!@#$%^&*()" ("*&-=+" int, "?>.,\/" text); CREATE TABLE postgres=# \d "<>!@#$%^&*()" Table "public.<>!@#$%^&*()" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- *&-=+ | integer | | | ?>.,\/ | text | | | postgres=# INSERT INTO "<>!@#$%^&*()" VALUES (1, 'hello world'); INSERT 0 1 postgres=# SELECT * FROM "<>!@#$%^&*()"; *&-=+ | ?>.,\/ -------+------------- 1 | hello world (1 row) -- Regrads, Japin Li ChengDu WenWu Information Technology Co., Ltd.