Why "=" in where clause, not match exact string (including spaces and special characters).

In this query I applied '$' Symbol for filtering repeated data on Data Table and null treated as empty strings. This query will return sum of all values inside one group in front of group name.

Issue is when data/string have spaces or special characters like "." in it it will not match these string in where clause and show null in front of these group instead of their some.

Schema (PostgreSQL v13)

CREATE TABLE IF NOT EXISTS products (
  id int NOT NULL,
  title varchar(200) NOT NULL,
  description varchar(200) NOT NULL,
  price int NOT NULL,
  PRIMARY KEY (id)
);


INSERT INTO products VALUES
  (1, 'test', 'test',2222),
  (2, 'test', 'test2',1111),
  (3, 'test3', 'test3',1111),
  (4, 'test3.2', 'test3.2',555),
  (5, 'test3.2', 'test3.3',1111),
  (6, 'test4', 'test4 desc',1111);

Query #1

create or replace function get_price_value(
  tablename regclass,
  sum_of_column_name character varying, 
  on_column_name character varying, 
  on_column_value text
) 
  returns int as 
'
declare total_sum integer;
begin
   EXECUTE FORMAT(''select sum(%I) from %I where %I=''''%I'''' ''
   ,sum_of_column_name
   ,tablename
   ,on_column_name
   ,on_column_value)
   INTO total_sum;
   return total_sum;
end;
'
language plpgsql;

There are no results to be displayed.

Query #2

select id, 
title, 
description,
price
from(
  select DISTINCT id, title, description, price,trno, drno
from (
  select id, 1, null, title, null, get_price_value('products'::regclass,'price','title',title)::varchar as price, 1 as trno, 2 as drno from products
  union all
  select id, 2, null, concat(title,'$$$'), description, get_price_value('products'::regclass,'price','description',description)::varchar as price, 2 as trno, 1 as drno from products
  union all
  select id, 3, id::varchar, concat(title,'$$$') as title, concat(description,'$$$') as description, price::varchar, 2 as trno, 2 as drno from products
) temp1 (xid, xord, id, title, description,price,trno,drno)
 order by title, trno, description, drno
) as temp2;

Output View in Data Table

Expected Output:

Please suggest any solution in sql. Thank you!

View on DB Fiddle

CodePudding user response：

The problem is the FORMAT(''where %I=''''%I'''' '' part. The second %I isn't an identifier, but a literal for which you should use %L without the enclosing '.