Question 176116 in PostgreSQL Question posted in
The official documentation can be found here.

How do I fix a syntax error when concatenating strings & appending to a PostgreSQL where clause?

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.

Tags:

2

Answers


  1. 0

    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$;
    Login or Signup to reply.
  2. 0

    It appears that the OP intended to dynamically build and then execute a query to return the number of ratings a company for multiple discrete ranges. If no ranges are selected, then the query should return the total number of ratings for the company. The OP’s code defined the ranges as closed intervals. I’ve implemented them as half-open intervals to protect against potential gaps between ranges.

    The following implements that capability as a PL/pgSQL function:

    CREATE OR REPLACE FUNCTION public.reviewsbystartotal(
  cid integer, stars integer[])
  RETURNS integer
  LANGUAGE plpgsql
  VOLATILE PARALLEL SAFE
AS
$BODY$
DECLARE
  appendtext  text := '';
  num_matched integer;
BEGIN
  IF 5 = ANY (stars) THEN
    appendtext := appendtext || ' and cr.generalrating >= 4.25';
  END IF;
  IF 4 = ANY (stars) THEN
    appendtext := appendtext || ' and cr.generalrating >= 3.25 and cr.generalrating < 4.25';
  END IF;
  IF 3 = ANY (stars) THEN
    appendtext := appendtext || ' and cr.generalrating >= 2.5 and cr.generalrating < 3.25';
  END IF;
  IF 2 = ANY (stars) THEN
    appendtext := appendtext || ' and cr.generalrating >= 1.75 and cr.generalrating < 2.5';
  END IF;
  IF 1 = ANY (stars) THEN
    appendtext := appendtext || ' and cr.generalrating >= 1 and cr.generalrating < 1.75';
  END IF;

  EXECUTE FORMAT($$SELECT COUNT(1) FROM companyreviews cr WHERE cr.company = $1 AND cr.internalapproval = TRUE %s$$,
                 appendtext)
    INTO num_matched
    USING cid;

  RETURN num_matched;
END
$BODY$;

    The desired behavior can be implemented as a SQL function without resorting to dynamic SQL as follows:

    CREATE OR REPLACE FUNCTION public.reviewsbystartotal(
  cid integer, stars integer[])
  RETURNS integer
  LANGUAGE SQL
  VOLATILE PARALLEL SAFE
AS
$BODY$
WITH ratings_ranges(star_level, ratings_range) AS (VALUES (1, NUMRANGE(1, 1.75)),
                                                          (2, NUMRANGE(1.75, 2.5)),
                                                          (3, NUMRANGE(2.5, 3.25)),
                                                          (4, NUMRANGE(3.25, 4.25)),
                                                          (5, NUMRANGE(4.25, NULL))),
     selected_ranges AS (SELECT ratings_ranges.ratings_range
                           FROM ratings_ranges
                           WHERE reviewsbystartotal.stars IS NULL
                              OR ARRAY_LENGTH(reviewsbystartotal.stars, 1) IS NULL
                              OR ratings_ranges.star_level = ANY (reviewsbystartotal.stars))
SELECT COUNT(*)
  FROM companyreviews cr
    JOIN selected_ranges sr
         ON cr.generalrating <@ sr.ratings_range
  WHERE cr.company = reviewsbystartotal.cid
    AND cr.internalapproval;
$BODY$;

    All column and variable references are qualified to protect against protential identifier ambiguity.

    There is one known difference in behavior between the PL/pgSQL and SQL implementations: if all items in the stars array have values outside of the valid range of 1-5, then the PL/pgSQL version will return the count of all ratings for the company and the SQL version will return a count of 0.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search