quote_ident() 不会为列名“first"添加引号； [英] quote_ident() does not add quotes to column name "first"

问题描述

我需要将列名正确地用双引号括起来.quote_ident() 好像不行?

先选1；-- 失败选择 quote_ident('first');-- 先生产，而不是先"；

我可以使用什么命令来成功引用标识符.我正在尝试用它动态构建一个 SELECT 语句:

SELECT '选择'||string_agg(udt_name 在 ('varchar', 'text') 中的情况然后 'left(' || quote_ident(column_name) || ', 65535) ' ||报价标识(列名称)否则quote_ident(列名)end, ',' 按 ordinal_position 排序)||' 来自public".MyTableName"'FROM information_schema.columns cjoin parse_ident('"public"."MyTableName"') t在 t[1] = table_schema 和 t[2] = table_name 上

这是生成:

SELECT id, left(first, 65535) first from "public"."MyTableName";

这是因为首先作为列名需要用双引号括起来.

解决方案

列别名不要省略AS关键字

<块引用>

SELECT id, left(first, 65535) first from "public"."MyTableName";

这是因为首先作为列名需要用双引号括起来.

不完全是.它爆炸是因为您在不应省略的地方省略了关键字 AS.

这有效:

SELECT 'select'||string_agg(udt_name 在 ('varchar', 'text') 中的情况然后 'left(' || quote_ident(column_name) || ', 65535) AS ' -- !!||报价标识(列名称)否则quote_ident(列名)end, ', ' 按 ordinal_position 排序)||' 来自public".MyTableName"'FROM information_schema.columns cjoin parse_ident('"public"."MyTableName"') t在 t[1] = table_schema 和 t[2] = table_name 上；

产生:

SELECT id, left(first, 65535) AS first from "public"."MyTableName";

依次按预期工作.

关于的手册"省略 AS 关键字:

<块引用>

在SQL标准中，可选关键字AS之前可以省略每当新列名是有效列时的输出列名名称(即，与任何保留关键字不同).PostgreSQL 是稍微严格一点:AS 如果新列名是必需的完全匹配任何关键字，保留与否.推荐做法是使用 AS 或双引号输出列名，以防止任何可能的与未来的关键字添加冲突.

可以省略表别名的关键字AS，但不能省略列别名.

first 不是 保留字 在 Postgres 中.(它曾经在古代 SQL 标准 SQL-92 中被保留"，但在标准 SQL 中也不再存在.)准确地说，它是 非保留"*.手册:

<块引用>

非保留关键词只在特定语境中具有特殊意义，在其他语境中可以作为标识符使用.

省略 AS 使其成为这样的上下文.

quote_ident() 工作可靠.手册:

<块引用>

返回适当引用的给定字符串以用作标识符在 SQL 语句字符串中.仅在必要时才添加引号(即如果字符串包含非标识符字符或将是案例折叠).嵌入的引号正确地加倍.

带有说明符 %I 的

format() 做同样的事情.

保留词未提及，但无论如何正确引用.准确地说:PostgreSQL"列中标记为reserved"或(不能是函数或类型)"的所有关键字都被标记为reserved"或(不能是函数或类型)".SQL 关键字表.

我将提交一个文档错误来添加它.

绝对确定:quote_all_identifiers

如果您想绝对确定并且不介意所有增加的噪音，您可以强制 Postgres 使用配置参数 quote_all_identifiers 引用 all 标识符.手册:

<块引用>

当数据库生成 SQL 时，强制引用所有标识符，即使它们不是(当前)关键字.

这包括来自 quote_ident() 和 format() 的输出.我不会那样做，害怕所有增加的噪音.

您可以在同一事务中使用 SET LOCAL 在本地设置参数.喜欢:

BEGIN;SET LOCAL quote_all_identifiers = true;选择 ...结尾;

更快

也就是说，我会使用 format() 和 concat() 并以目录表 pg_attribute 为目标:更干净、更简单、更快.但不能移植到其他 RDBMS:

SELECT format('SELECT %s FROM %s;', string_agg(CASE WHEN atttypid = ANY ('{text, bpchar, varchar}'::regtype[])THEN concat('left(', col, ', 65535) AS ', col)ELSE col END, ', '), 胆怯)从  (SELECT attrelid::regclass, atttypid, quote_ident(attname) AS col从 pg_catalog.pg_attributeWHERE attrelid = 'public.'MyTableName''::regclass -- 提供一次，可选模式限定并且 attnum >0并且没有被拒绝按 attnum 订购) 子GROUP BY attrelid;

产生:

SELECT id, left(first, 65535) AS first FROM MyTableName";

db<>fiddle 此处

值得注意的是，...

• ...您只需要提供一次表名，可选择模式限定.
• ...如果表不存在，查询会立即失败并显示有用的错误消息.
• ... 输出表名称仅在必要时使用模式限定和双引号.
• ...这也包括 character(N)(内部名称 bpchar).

进一步阅读:

• 如何检查如果给定模式中存在表
• 在 postgres psql 中默认截断显示选择语句
• PostgreSQL 强制所有数据大写
• 检查字符类型列中是否存在空字符串

I need to get a column name correctly wrapped in double quotes. quote_ident() does not seem to do it?

select 1 first; -- fails
select quote_ident('first'); -- produces first, not "first"

What command can I use to successfully quote an identifier. I am trying to dynamically build a SELECT statement with it:

SELECT 'select ' 
|| string_agg(
        case when udt_name in ('varchar', 'text')
            then 'left(' || quote_ident(column_name) || ', 65535) ' || quote_ident(column_name)
        else quote_ident(column_name)
        end, ',' order by ordinal_position) 
|| ' from "public"."MyTableName"'
FROM information_schema.columns c
join parse_ident('"public"."MyTableName"') t 
on t[1] = table_schema and t[2] = table_name

This is generating:

SELECT id, left(first, 65535) first from "public"."MyTableName";

Which blows up because first as a column name needs to be wrapped in double quotes.

解决方案

Don't omit AS key word for column aliases

SELECT id, left(first, 65535) first from "public"."MyTableName";

Which blows up because first as a column name needs to be wrapped in double quotes.

Not exactly. It blows up because you omitted the keyword AS where it should not be omitted.

This works:

SELECT 'select ' 
|| string_agg(
        case when udt_name in ('varchar', 'text')
            then 'left(' || quote_ident(column_name) || ', 65535) AS '  -- !!
              ||  quote_ident(column_name)
        else quote_ident(column_name)
        end, ', ' order by ordinal_position) 
|| ' from "public"."MyTableName"'
FROM information_schema.columns c
join parse_ident('"public"."MyTableName"') t 
on t[1] = table_schema and t[2] = table_name;

Produces:

SELECT id, left(first, 65535) AS first from "public"."MyTableName";

Which works as expected in turn.

The manual about "Omitting the AS Key Word":

In the SQL standard, the optional key word AS can be omitted before an output column name whenever the new column name is a valid column name (that is, not the same as any reserved keyword). PostgreSQL is slightly more restrictive: AS is required if the new column name matches any keyword at all, reserved or not. Recommended practice is to use AS or double-quote output column names, to prevent any possible conflict against future keyword additions.

It's OK to omit the keyword AS for table aliases, but not for column aliases.

first is not a reserved word in Postgres. (It used to be "reserved" in the ancient SQL standard SQL-92, but not any more in standard SQL, either.) It is "non-reserved"* to be precise. The manual:

Non-reserved key words only have a special meaning in particular contexts and can be used as identifiers in other contexts.

Omitting AS makes it just such a context.

quote_ident() works reliably. The manual:

Returns the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled.

format() with the specifier %I does the same.

Reserved words are not mentioned, but quoted properly regardless. To be precise: all key words marked "reserved" or "(cannot be function or type)" in the column "PostgreSQL" of the SQL Key Words table.

I'll file a documentation bug to add that.

To be absolutely sure: quote_all_identifiers

If you want to be absolutely sure and don't mind all the added noise, you can force Postgres to quote all identifiers with the configuration parameter quote_all_identifiers. The manual:

When the database generates SQL, force all identifiers to be quoted, even if they are not (currently) keywords.

That includes output from quote_ident() and format(). I would not do that, dreading all the added noise.

You might set the parameter locally with SET LOCAL in the same transaction. Like:

BEGIN;
SET LOCAL quote_all_identifiers = true;
SELECT ...
END;

Faster

That said, I would use format() and concat() and target the catalog table pg_attribute instead: cleaner, simpler, faster. But not portable to other RDBMS:

SELECT format('SELECT %s FROM %s;'
            , string_agg(CASE WHEN atttypid = ANY ('{text, bpchar, varchar}'::regtype[])
                              THEN concat('left(', col, ', 65535) AS ', col)
                              ELSE col END, ', ')
            , attrelid)
FROM  (
   SELECT attrelid::regclass, atttypid, quote_ident(attname) AS col
   FROM   pg_catalog.pg_attribute
   WHERE  attrelid = 'public."MyTableName"'::regclass  -- provide once, optionally schema-qualified
   AND    attnum > 0
   AND    NOT attisdropped
   ORDER  BY attnum
   ) sub
GROUP  BY attrelid;

Produces:

SELECT id, left(first, 65535) AS first FROM "MyTableName";

db<>fiddle here

Notably, ...

• ... you only need to provide the table name once, optionally schema-qualified.
• ... if the table does not exist, the query fails immediately with a helpful error message.
• ... the output table name is only schema-qualified and double-quoted where necessary.
• ... this also covers character(N) (internal name bpchar).

Further reading:

• How to check if a table exists in a given schema
• Truncating display by default in postgres psql select statements
• PostgreSQL force upper case for all data
• Check whether empty strings are present in character-type columns

这篇关于quote_ident() 不会为列名“first"添加引号；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！