如何避免在PostgreSQL中的循环中添加额外的报价

发布于 2025-01-23 04:44:06 字数 598 浏览 0 评论 0原文

当在值的表上执行一个循环时，“我会得到这些引号加倍，例如：初始输入：“测试和“测试”' 循环中的输出：'测试和“测试”''

如何复制：

CREATE TABLE roles(
   id int,
   criteria VARCHAR (255)
);

INSERT INTO roles (id, criteria) VALUES (1, 'test AND "test"');


CREATE OR REPLACE FUNCTION test_quote()
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
DECLARE
sample varchar;
rec   record;
BEGIN
    FOR rec IN
        SELECT * FROM roles
    LOOP
        sample:= rec;
    END LOOP;
   RETURN sample;
END
$$;

SELECT * FROM test_quote();

预期结果：（1，“测试”和“测试”） 实际结果：（1，“测试”和“测试”“”）

有人知道如何在这里获得预期行为吗？

原文

When performing a loop on a table in which a value has ", I get those quotes doubled such as:
initial input: 'test AND "test"'
output in the loop: 'test AND ""test""'

How to reproduce:

CREATE TABLE roles(
   id int,
   criteria VARCHAR (255)
);

INSERT INTO roles (id, criteria) VALUES (1, 'test AND "test"');


CREATE OR REPLACE FUNCTION test_quote()
RETURNS VARCHAR
LANGUAGE plpgsql
AS $
DECLARE
sample varchar;
rec   record;
BEGIN
    FOR rec IN
        SELECT * FROM roles
    LOOP
        sample:= rec;
    END LOOP;
   RETURN sample;
END
$;

SELECT * FROM test_quote();

Expected result: (1,"test AND "test"")
Actual result: (1,"test AND ""test""")

Does anyone have an idea how to get the expected behaviour here?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

垂暮老矣 2025-01-30 04:44:06

实际上，发现它是从从记录到Varchar的转换。

我可以直接从rec中传递列，例如：

示例：= rec.criteria;

（我不知道我们可以做到这一点）

所以，这给了：

CREATE TABLE roles(
   id int,
   criteria VARCHAR (255)
);

INSERT INTO roles (id, criteria) VALUES (1, 'test AND "test"');


CREATE OR REPLACE FUNCTION test_quote()
RETURNS VARCHAR
LANGUAGE plpgsql
AS $
DECLARE
sample varchar;
rec   record;
BEGIN
    FOR rec IN
        SELECT * FROM roles
    LOOP
        sample := rec.criteria;
    END LOOP;
   RETURN sample;
END
$;

SELECT * FROM test_quote();

THX家伙！

Found it, indeed, the issue is from the conversion from record to varchar.

I can directly pass the column from the rec like:

sample:= rec.criteria;

(I didn't know that we could do that)

So, that gives:

CREATE TABLE roles(
   id int,
   criteria VARCHAR (255)
);

INSERT INTO roles (id, criteria) VALUES (1, 'test AND "test"');


CREATE OR REPLACE FUNCTION test_quote()
RETURNS VARCHAR
LANGUAGE plpgsql
AS $
DECLARE
sample varchar;
rec   record;
BEGIN
    FOR rec IN
        SELECT * FROM roles
    LOOP
        sample := rec.criteria;
    END LOOP;
   RETURN sample;
END
$;

SELECT * FROM test_quote();

Thx guys!

回复收藏 0 原文

~没有更多了~