PL/pgSQL中的%%是什么意思? [英] What does %% in PL/pgSQL mean?

问题描述

我正在阅读 Instagram的分片解决方案，我注意到以下行:

I was reading over Instagrams sharding solution and I noticed the following line:

SELECT nextval('insta5.table_id_seq') %% 1024 INTO seq_id;

上面的SELECT行中的%%有什么作用? 我查看了PostgreSQL，发现的唯一发现是，当您想使用文字百分比字符时，会使用%%.

What does the %% in the SELECT line above do? I looked up PostgreSQL and the only thing I found was that %% is utilized when you want to use a literal percent character.

CREATE OR REPLACE FUNCTION insta5.next_id(OUT result bigint) AS $$
DECLARE
   our_epoch bigint := 1314220021721;
   seq_id bigint;
   now_millis bigint;
   shard_id int := 5;
BEGIN
   SELECT nextval('insta5.table_id_seq') %% 1024 INTO seq_id;

   SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
   result := (now_millis - our_epoch) << 23;
   result := result | (shard_id << 10);
   result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;

推荐答案

我能想到的唯一的 地方，在%翻倍="http://www.postgresql.org/docs/current/interactive/functions-string.html#FUNCTIONS-STRING-FORMAT" rel ="nofollow noreferrer"> format() 函数，通常用于生成动态SQL的查询字符串. 在此处比较示例.

The only place I can think of, where a % would be doubled up in standard Postgres is inside the format() function, commonly used for producing a query string for dynamic SQL. Compare examples here on SO.

手册:

除上述格式说明符外，特殊 序列%%可用于输出文字%字符.

In addition to the format specifiers described above, the special sequence %% may be used to output a literal % character.

使用模数时棘手动态语句中的运算符% ！

我怀疑他们正在幕后运行动态SQL-他们在本文中对其进行了概括和简化. (序列的模式限定名称为'insta5.table_id_seq'，并且该表将不被命名为表".)在此过程中，他们忘记了对模运算符进行转义".
那就是他们实际正在运行的东西:

I suspect they are running dynamic SQL behind the curtains - which they generalized and simplified for the article. (The schema-qualified name of the sequence is 'insta5.table_id_seq' and the table wouldn't be named "table".) In the process they forgot to "unescape" the modulo operator.
That's what they may actually be running:

EXECUTE format($$SELECT nextval('%I') %% 1024$$, seq_name)
INTO seq_id;

这篇关于PL/pgSQL中的%%是什么意思?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！