PL/pgSQL 中的变量替换

发布于 2024-10-05 06:52:27 字数 606 浏览 3 评论 0原文

我有一个根据提供的参数动态生成的选择语句。问题是postgresql总是说：

WHERE的参数必须是布尔类型，而不是字符变化类型无论参数是什么。我错过了什么吗？

CREATE OR REPLACE FUNCTION getuid(name character varying) RETURNS integer AS $$
    DECLARE 
        statement varchar;
        uid integer;
    BEGIN
        IF ($1 = '') THEN
            statement := 'TRUE';
            statement := CAST(statement AS BOOLEAN);
        ELSE
            statement := 'users.keywords ILIKE''' || '%' || $1 || '%''';
        END IF;
        SELECT INTO uid id FROM users WHERE "statement";
        RETURN uid;
    END;
$$ LANGUAGE plpgsql

原文

I have a select statement that is generated dynamically based on the supplied parameter. The problem is that postgresql always says:

argument of WHERE must be type boolean, not type character varying no matter what the parameter is. Did I miss anything?

CREATE OR REPLACE FUNCTION getuid(name character varying) RETURNS integer AS $
    DECLARE 
        statement varchar;
        uid integer;
    BEGIN
        IF ($1 = '') THEN
            statement := 'TRUE';
            statement := CAST(statement AS BOOLEAN);
        ELSE
            statement := 'users.keywords ILIKE''' || '%' || $1 || '%''';
        END IF;
        SELECT INTO uid id FROM users WHERE "statement";
        RETURN uid;
    END;
$ LANGUAGE plpgsql

分享到QQ

分享到微博

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

发布评论

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

知你几分 2024-10-12 06:52:27

如果您想要在函数内生成动态命令。您还可以使用两个不同的部分：

CREATE OR REPLACE FUNCTION getuid(name character varying) RETURNS integer AS $
    DECLARE 
        statement varchar;
        uid integer;
    BEGIN
        IF ($1 = '' OR $1 IS NULL) THEN -- section 1
            SELECT id INTO uid FROM users;
        ELSE -- section 2
            SELECT id INTO uid FROM users WHERE users.keywords ILIKE '%' || $1 || '%';
        END IF;

        RETURN uid;
    END;
$ LANGUAGE plpgsql;

You need EXECUTE if you want to generate dynamic commands inside a function. You could also use two different sections:

CREATE OR REPLACE FUNCTION getuid(name character varying) RETURNS integer AS $
    DECLARE 
        statement varchar;
        uid integer;
    BEGIN
        IF ($1 = '' OR $1 IS NULL) THEN -- section 1
            SELECT id INTO uid FROM users;
        ELSE -- section 2
            SELECT id INTO uid FROM users WHERE users.keywords ILIKE '%' || $1 || '%';
        END IF;

        RETURN uid;
    END;
$ LANGUAGE plpgsql;

回复收藏 0 原文