Home Getting a syntax error when concatenating string and appending it to the where clause in PostgreSQL. How to fix?

Questions

Getting a syntax error when concatenating string and appending it to the where clause in PostgreSQL. How to fix?

May 26, 2023

I am trying to create a dynamic string and append it to where clause in postgresql byt I always get an error stating;

ERROR:  syntax error at or near "4"
LINE 13:      when 4 = ANY(stars) then appendtext := appendtext || ' ...
                   ^ 

SQL state: 42601
Character: 332

This is the function

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$;

I tried to concanate a string and append it where clause in postgresql.

>Solution :

The concatenation operator || should be placed outside the parentheses of the SELECT statement.

Here’s the corrected version of your function:

    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';
    END CASE;

    RETURN (
        SELECT COUNT(1)
        FROM companyreviews cr
        WHERE cr.company = cid
        AND cr.internalapproval = true
    ) || appendtext;

END;
$BODY$;