postgresql - 如何在 for 循环中处理 SELECT 的两个选项

Question

我在函数中有以下代码...

CREATE FUNCTION blah(
    p_task_id                 INTEGER
)
RETURNS BOOLEAN AS $$
DECLARE
    v_name                    CHAR(30);
    v_age                     INTEGER;
    v_select_string           TEXT;
BEGIN
    ...

        IF v_name = '' AND v_age = 0 THEN
            v_select_string =  'SELECT * FROM my_table
                                    WHERE id = p_id';
        ELSE
            v_select_string =  'SELECT * FROM my_table 
                                    WHERE name = ' || v_name || '
                                    AND   age  = ' || v_age  || '
                                    AND   id   = p_id';
        END IF;

        FOR s1 IN (
            v_select_string
                )
        LOOP

            ...

        END LOOP;

......但我得到这个......

ERROR:  syntax error at or near "v_select_string"

有没有办法解决这个问题？循环的内容很大，所以我宁愿不要在循环中复制循环IF / ELSE

标签： postgresql

Answer 1

您不能只将字符串传递给 FOR 循环。您需要使用关键字将其作为动态 SQL运行。execute

FOR s1 IN EXECUTE v_select_string
LOOP
  ...
END LOOP;