postgres格式不会用提供的值替换类型参数

wyyhbhjk 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(347)

老实说，我对postgres和sql没有太多经验。
我正在尝试使用executeformat（…）查询生成具有动态表和列名的函数。但是我尝试的任何格式都不考虑为类型参数（如%s和%i）提供的值。由于缺乏有用的信息，谷歌也没能帮上忙。我看到很多例子，我的代码没有区别于例子，但不工作。我在这里做了小提琴->db小提琴
以下是我的ddl代码：

CREATE TABLE IF NOT EXISTS tbl (
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL
);

CREATE FUNCTION occurences_number(varchar, varchar) RETURNS integer AS ' 
DECLARE
  text varchar := LOWER($1);
  str varchar  := LOWER($2);
BEGIN
  RETURN (CHAR_LENGTH(text) - CHAR_LENGTH(REPLACE(text, str, " "))) / CHAR_LENGTH(str);
END; '
LANGUAGE PLPGSQL;

CREATE FUNCTION records_with_string(regclass, varchar, varchar)
RETURNS integer AS '
DECLARE
  result integer;
  tbl ALIAS FOR $1;
  col ALIAS FOR $2;
  str ALIAS FOR $3;
BEGIN
  EXECUTE format("SELECT COUNT(*) FROM %I WHERE occurences_number(%I, %s) > 0", tbl::varchar, col::varchar, str::varchar)
  INTO result;
  RETURN result;
END; '
LANGUAGE PLPGSQL;

下面是问题：

INSERT INTO tbl VALUES ('a', 'aaa bbb ccc ddd'), ('b', 'aaa bbb'), ('c', 'ccc ddd'), ('d', 'aaa ccc ddd'), ('e', 'aaa bbb ccc ddd eee');

SELECT "records_with_string"('tbl'::regclass, 'last_name', 'eee') FROM tbl;

查询结果为：
查询错误：错误：列“select count（*）from%i where occurrences\u number（%i，%s）>0”不存在
我做错了什么？
更新db fiddle不接受$$

sql postgresql fiddle

来源：https://stackoverflow.com/questions/62403885/postgres-format-doesnt-replace-type-argument-with-provided-value

2条答案

按热度按时间

gv8xihay1#

不应该对字符串文字使用双引号，而应该使用单引号 %L 当使用 format :

select * from tbl;
 first_name |      last_name      
------------+---------------------
 a          | aaa bbb ccc ddd
 b          | aaa bbb
 c          | ccc ddd
 d          | aaa ccc ddd
 e          | aaa bbb ccc ddd eee
(5 rows)

CREATE OR REPLACE FUNCTION occurences_number(varchar, varchar) RETURNS integer AS 
$$
DECLARE
  text varchar := LOWER($1);
  str varchar  := LOWER($2);
BEGIN
  RETURN (CHAR_LENGTH(text) - CHAR_LENGTH(REPLACE(text, str, ' '))) / CHAR_LENGTH(str);
END;
$$ 
LANGUAGE PLPGSQL;
CREATE FUNCTION

CREATE OR REPLACE FUNCTION records_with_string(tbl varchar, col varchar, str varchar)
RETURNS integer AS
$$ 
DECLARE
  result integer;
BEGIN
  EXECUTE format('SELECT COUNT(*) FROM %I WHERE occurences_number(%L, %L) > 0', tbl, col, str)
  INTO result;
  RETURN result;
END;
$$ 
LANGUAGE PLPGSQL;
CREATE FUNCTION

SELECT "records_with_string"('tbl', 'last_name', 'eee') FROM tbl;
 records_with_string 
---------------------
                   0
                   0
                   0
                   0
                   0
(5 rows)

赞(0）回复(0）举报 2021-07-26

lrl1mhuk2#

为了避免与普通引号冲突，您需要用 dollar quotes （基本上是两个 $ 中间有东西的角色。某些内容可以为空，但开始引号和结束引号必须匹配）：

CREATE TABLE IF NOT EXISTS ztbl (
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL
);

INSERT INTO ztbl(first_name, last_name) VALUES ('Barack' , 'Obama') , ('Donald' , 'Trump') ;

CREATE FUNCTION occurences_number(varchar, varchar) RETURNS integer AS
$zz$ -- <<-- here
DECLARE
  txt varchar := LOWER($1);
  str varchar := LOWER($2);
BEGIN
  RETURN (CHAR_LENGTH(txt) - CHAR_LENGTH(REPLACE(txt, str, '' ))) / CHAR_LENGTH(str);
END;
$zz$ -- <<-- here
LANGUAGE PLPGSQL;

CREATE FUNCTION records_with_string(regclass, varchar, varchar)
RETURNS integer AS
$zzz$ -- <<-- here
DECLARE
  result integer;
  tbl ALIAS FOR $1;
  col ALIAS FOR $2;
  str ALIAS FOR $3;
BEGIN
  EXECUTE format('
        SELECT COUNT(*) FROM %I
        WHERE occurences_number(%I, %L ) > 0 -- <<-- here
        ;', tbl::varchar, col::varchar, str::varchar)
  INTO result;
  RETURN result;
END;
$zzz$ -- <<-- here
LANGUAGE PLPGSQL;

SELECT records_with_string('ztbl', 'first_name', 'a');

赞(0）回复(0）举报 2021-07-26

我来回答

postgres格式不会用提供的值替换类型参数

2条答案

相关问题

热门标签

最新问答