包含条件的字符串的if语句 [英] if-statement with string containing the condition

问题描述

此问题与 Postgresql 8.3 有关.

我有一个表，该表的字段包含诸如"lastcontact为null"之类的条件.在代码中，我想遍历该表，并且对于每个记录，我都想检查如果条件那么"，如下面的示例所示:

I've got a table with a field containing conditions like 'lastcontact is null'. In code, I want to loop through this table and for each record, I want to check 'if condition then', like in this example:

FOR myrec IN
    SELECT * FROM tabel ORDER BY colorlevel, volgnummer
LOOP
    if (myrec.conditie) then
        raise notice 'Condition % is true', myrec.conditie;
    else
        raise notice 'Condition % is false', myrec.conditie;
    end if;
END LOOP;

在此示例中我称为"tabel"的表:

The table which I have called 'tabel' in this example:

ID | Conditie             | Colorlevel | Volgnummer | Code | Description
1  | lastcontact is null  | 1          | 1          | ...  | ...
2  | lastchanged is null  | 1          | 2          | ...  | ...
3  | lastmodified is null | 1          | 3          | ...  | ...

是否可以进行我想要的检查?上面的代码导致以下错误:

Is it possible to do the check I desire? The code above results in the following error:

ERROR:  invalid input syntax for type boolean: "lastcontact is null"

---

包含Erwin函数结果的新部分

---

New section containing the result of Erwin's function

我已经使用了此功能:

CREATE OR REPLACE FUNCTION foo(lastcontact timestamptz)
  RETURNS void AS
$BODY$
DECLARE
  myrec record;
  mycond boolean;
BEGIN

FOR myrec IN
    SELECT * FROM tabel ORDER BY colorlevel, volgnummer
LOOP
    EXECUTE 'SELECT ' || myrec.conditie || ' FROM tabel' INTO mycond;

    IF mycond then
        RAISE NOTICE 'Condition % is true', myrec.conditie;
    ELSE
        RAISE NOTICE 'Condition % is false', COALESCE(myrec.conditie, 'NULL');
    END IF;
END LOOP;

END;
$BODY$
language 'plpgsql' volatile
cost 100;

我收到此错误:

ERROR:  column "lastcontact" does not exist
LINE 1: SELECT lastcontact is null FROM tabel
           ^
QUERY:  SELECT lastcontact is null FROM tabel
CONTEXT:  PL/pgSQL function "foo" line 9 at EXECUTE statement1

我试图自己找到一种解释，但无济于事.显然，它试图在数据库上运行该语句，但它应该理解'lastcontact'是作为函数参数给出的变量.

I tried to find an explanation myself, but to no avail. It obviously is trying to run the statement against the database, but it should understand that 'lastcontact' is the variable that's been given as a function parameter.

推荐答案

从这些评论中，我终于认为我理解了.您需要 动态SQL :

From the comments I finally think I understand. You need dynamic SQL:

CREATE OR REPLACE FUNCTION foo(lastcontact timestamptz)
  RETURNS void AS
$func$
DECLARE
   myrec  record;
   mycond boolean;
BEGIN

FOR myrec IN
    SELECT * FROM tabel ORDER BY colorlevel, volgnummer
LOOP
    IF myrec.conditie ~~ '%lastcontact %' THEN   -- special case for input param
        myrec.conditie := replace (myrec.conditie
                        , 'lastcontact '
                        , CASE WHEN lastcontact IS NULL THEN 'NULL '
                          ELSE '''' || lastcontact::text || ''' ' END);
    END IF;

    EXECUTE 'SELECT ' || myrec.conditie || ' FROM tabel' INTO mycond;

    IF mycond then
        RAISE NOTICE 'Condition % is true', myrec.conditie;
    ELSE
        RAISE NOTICE 'Condition % is false', COALESCE(myrec.conditie, 'NULL');
    END IF;
END LOOP;

END
$func$  LANGUAGE plpgsql;

但是请注意，此设置可用于 SQL注入.仅使用经过验证的输入. 一个函数也可以在PostgreSQL 8.3 中使用(还没有DO语句).

Be aware, however, that this setup is wide open for SQL injection. Only use verified input. A function works in PostgreSQL 8.3 as well (no DO statements, yet).

您不能在动态SQL(EXECUTE语句)中引用参数.您必须将值放入查询字符串中.

You cannot refer to parameters inside dynamic SQL (EXECUTE statement). You have to put the value into the query string.

在PostgreSQL 8.4或更高版本中，您具有 USING子句. las，不是8.3版.如果可以的话，您应该考虑升级.

In PostgreSQL 8.4 or later you have the superior commodity of the USING clause. Alas, not in version 8.3. You should consider upgrading if you can.

我为您的旧版本提供了一种解决方法.您必须特别注意NULL值.

I put in a workaround for your old version. You have to take special care of the NULL value.

这篇关于包含条件的字符串的if语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！