了解PL/pgSQL函数中的int文字和int参数之间的区别 [英] Understanding difference between int literal vs int parameter in PL/pgSQL function

问题描述

我有一个函数可以在PostgreSQL 9.5中保留位的刺痛:

I have a function to left pad bit stings in PostgreSQL 9.5:

CREATE OR REPLACE FUNCTION lpad_bits(val bit varying) 
RETURNS bit varying as
$BODY$
  BEGIN return val::bit(32) >> (32-length(val));
  END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;

工作正常:

 # select lpad_bits(b'1001100111000');
        lpad_bits
 ----------------------------------
 00000000000000000001001100111000
 (1 row)

我的问题是，当我尝试添加参数以更改填充量时:

My problem is when I try to add a parameter to change the amount of padding:

CREATE OR REPLACE FUNCTION lpad_bits(val bit varying, sz integer default 1024) 
  RETURNS bit varying as
$BODY$
  BEGIN return val::bit(sz) >> (sz-length(val));
  END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;

该功能现在已损坏:

# select lpad_bits(b'1001100111000', 32);                                      

ERROR:  invalid input syntax for integer: "sz"
LINE 1: SELECT val::bit(sz) >> (sz-length(val))
                ^
QUERY:  SELECT val::bit(sz) >> (sz-length(val))
CONTEXT:  PL/pgSQL function lpad_bits(bit varying,integer) line 2 at RETURN

我盯着位字符串文档和 PL/pgSQL函数文档，根本不是了解这两种实现之间的根本区别.

I have stared at the bitstring documentation and PL/pgSQL function documentation, am simply not seeing what is fundamentally different between these two implementations.

推荐答案

为什么?

PL/pgSQL执行SQL查询，例如 准备好的语句 . 有关参数替换的手册:

准备好的语句可以采用参数:被替换的值 在执行该语句时进入该语句.

Prepared statements can take parameters: values that are substituted into the statement when it is executed.

请注意术语 值 .只能参数化实际值，而不能关键字，标识符或类型名. bit(32) 中的32看起来像值，但是数据类型的修饰符在内部仅是一个值"，无法进行参数化. SQL要求在计划阶段知道数据类型，因此不能等待执行阶段.

Note the term values. Only actual values can be parameterized, but not key words, identifiers or type names. 32 in bit(32) looks like a value, but the modifier of a data type is only a "value" internally and can't be parameterized. SQL demands to know data types at planning stage, it cannot wait for the execution stage.

您可以通过动态SQL和EXECUTE实现您的目标.作为概念证明:

You could achieve your goal with dynamic SQL and EXECUTE. As proof of concept:

CREATE OR REPLACE FUNCTION lpad_bits(val varbit, sz int = 32, OUT outval varbit) AS
$func$
BEGIN
   EXECUTE format('SELECT $1::bit(%s) >> $2', sz)  -- literal
   USING val, sz - length(val)                     -- values
   INTO outval;
END
$func$  LANGUAGE plpgsql IMMUTABLE;

致电:

SELECT lpad_bits(b'1001100111000', 32);  

请注意，将sz用作 文字 来构建语句与将其用作 value ，可以将其作为参数传递.

Note the distinction between sz being used as literal to build the statement and its second occurrence where it's used as value, that can be passed as parameter.

针对此特定任务的出色解决方案是仅使用 lpad() ，例如

A superior solution for this particular task is to just use lpad() like @Abelisto suggested:

CREATE OR REPLACE FUNCTION lpad_bits2(val varbit, sz int = 32)
  RETURNS varbit AS
$func$
SELECT lpad(val::text, sz, '0')::varbit;
$func$  LANGUAGE sql IMMUTABLE;

(作为简单的SQL函数，它还可以在外部查询的上下文中实现函数内联.)

(Simpler as plain SQL function, which also allows function inlining in the context of outer queries.)

比上述功能快几倍.一个小缺陷:我们必须强制转换为text，然后再转换回varbit.不幸的是，lpad()当前尚未为varbit实现. 手册:

Several times faster than the above function. A minor flaw: we have to cast to text and back to varbit. Unfortunately, lpad() is not currently implemented for varbit. The manual:

以下SQL标准函数可用于位字符串以及 字符串:length，bit_length，octet_length，position，substring，overlay.

The following SQL-standard functions work on bit strings as well as character strings: length, bit_length, octet_length, position, substring, overlay.

overlay() 可用，我们可以提供更便宜的功能:

overlay() is available, we can have a cheaper function:

CREATE OR REPLACE FUNCTION lpad_bits3(val varbit, base varbit = '00000000000000000000000000000000')
  RETURNS varbit AS
$func$
SELECT overlay(base PLACING val FROM bit_length(base) - bit_length(val))
$func$  LANGUAGE sql IMMUTABLE;

如果可以使用varbit值开始的话，则更快. (如果必须将text强制转换为varbit，则优势是(部分地)无效了.)

Faster if you can work with varbit values to begin with. (The advantage is (partly) voided, if you have to cast text to varbit anyway.)

致电:

SELECT lpad_bits3(b'1001100111000', '00000000000000000000000000000000');
SELECT lpad_bits3(b'1001100111000',  repeat('0', 32)::varbit);

我们可能会 覆盖 带有变体的函数，该函数采用一个整数来生成base本身:

CREATE OR REPLACE FUNCTION lpad_bits3(val varbit, sz int = 32)
  RETURNS varbit AS
$func$
SELECT overlay(repeat('0', sz)::varbit PLACING val FROM sz - bit_length(val))
$func$  LANGUAGE sql IMMUTABLE;

致电:

SELECT lpad_bits3(b'1001100111000', 32;

相关:

• Postgresql将不同的位转换为整数
• 将文本表示形式的十六进制转换为十进制数字

• Postgresql Convert bit varying to integer
• Convert hex in text representation to decimal number

这篇关于了解PL/pgSQL函数中的int文字和int参数之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！