类似:以pgsql中的变量值结尾的通配符

Question

我已经这样写了我的pgsql块，其中我从这个块的上部接收var的值：

DO $$
declare
var integer ;
b integer;
k integer;
p integer;
m integer;
n varchar(100);
begin
k := null;
var := (select
count(child.relname) AS child
FROM pg_inherits
JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='abc');

while k = null loop
k := (select
child.relname AS child
FROM pg_inherits
JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='abc' and child.relname like '%',var);
var := var + 1;
end loop;

这不是完整的区块。但是，从块中您可以理解，我需要找出以变量名结尾的分区表(值(。然而，当我运行这个程序时，我会得到以下错误：

ERROR:  syntax error at or near ","
LINE 34: ...RE parent.relname='abc' and child.relname like '%',var);

我尝试使用双%%符号(一个用于通配符，另一个用于获取变量值，然后我得到以下错误：

ERROR:  syntax error at or near ","
LINE 34: ...E parent.relname='abc' and child.relname like '%%',var);

请告诉我是否有其他方法可以使用通配符访问pgsql变量值。

Answer 1

您需要使用字符串串联：

... child.relname LIKE ('%' || var)