我正在try 创建一个动态字符串,并将其附加到PostgreSQL的WHERE子句中.
ERROR: syntax error at or near "4"
LINE 13: when 4 = ANY(stars) then appendtext := appendtext || ' ...
^
SQL state: 42601
Character: 332
这就是功能
CREATE OR REPLACE FUNCTION public.reviewsbystartotal(
cid integer, stars integer[])
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
declare appendtext text := '';
BEGIN
case when 5 = ANY(stars) then appendtext := appendtext || ' and cr.generalrating >= 4.25'
when 4 = ANY(stars) then appendtext := appendtext || ' and cr.generalrating >= 3.25 and cr.generalrating <= 4.24'
when 3 = ANY(stars) then appendtext := appendtext || ' and cr.generalrating >= 2.5 and cr.generalrating <= 3.24'
when 2 = ANY(stars) then appendtext := appendtext || ' and cr.generalrating >= 1.75 and cr.generalrating <= 2.49'
when 1 = ANY(stars) then appendtext := appendtext || ' and cr.generalrating >= 1 and cr.generalrating <= 1.74'
else
RETURN (Select count(1) from companyreviews cr where cr.company=cid
and cr.internalapproval=true
|| appendtext);
END;
$BODY$;
我try 拼接一个字符串并将其附加到PostgreSQL中的WHERE子句中.