sql - 如何将变量连接到postgresql中的其他变量？

Question

在 php 中使用 .(点)连接变量，如 $a = "tes", $b="b", if
我连接 $a.$b 它变成了“tesb”。我想要这个但是在 postgres 中

我试过用点和+但是错了

我的代码

CREATE OR REPLACE FUNCTION make_ctx_contoh (rl varchar, dat varchar)
RETURNS numeric AS $total$
declare
date_before date;
dan text;
os_l numeric;
BEGIN
date_before = (DATE_TRUNC('month', NOW()) 
    + '0 MONTH'::INTERVAL 
    - '1 DAY'::INTERVAL)::DATE;
if rl = 'nasional' THEN
dan = '';
ELSEIF rl = 'kanwil' THEN
dan = 'AND LOWER(a."KANWIL") = knw';
ELSEIF rl = 'kc' THEN
dan = 'AND LOWER(a."KC") = kac';
END IF;
SELECT
    SUM("a"."OUTSTANDING") into os_l
    FROM
    "public".tbl_nominatif_hasil AS "a"
    WHERE
    "a"."BUSS_DATE" = date_before AND
    "a"."COLLDET" = '1 ' + dan; RETURN os_l;
END; $total$ LANGUAGE plpgsql;

当我运行 select make_ctx_contoh('kanwil','2'); 时显示如下错误:

ERROR: operator does not exist: unknown + text LINE 6:
"a"."COLLDET" = '1 ' + dan

Answer 1

据我从您的代码中了解到，您希望根据特定条件向 where 子句添加条件。

因此，将您的选择查询转换为 AND OR 逻辑。

SELECT
    SUM("a"."OUTSTANDING") into os_l
       FROM "public".tbl_nominatif_hasil AS "a"
    WHERE "a"."BUSS_DATE" = date_before AND
    "a"."COLLDET" = 1 
    AND ( rl = 'nasional' OR 
        ( rl = 'kanwil' AND LOWER(a."KANWIL") = knw) OR
        ( rl = 'kc'     AND LOWER(a."KC") = kac')
        )