PostgreSQL - ALL ( array ) Operator - Suggestion

Question

Sample Code as follows : ALL or ANY operator is not working. I need to compare ALL the values of the array

CREATE OR REPLACE FUNCTION public.sample_function(
    tt_sample_function text)
    RETURNS TABLE (..... )
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000

AS $BODY$
declare 

    e record;
    v_cnt INTEGER:=0;
    rec record;
    str text;
    a_v text [];
    

BEGIN

    FOR rec IN ( SELECT * FROM json_populate_recordset(null::sample_function ,sample_function::json) )
        LOOP
            a_v:= array_append(a_v, ''''||rec.key || '#~#' || rec.value||'''');

        END LOOP;
        
    SELECT MAInfo.userid FROM
        (SELECT DISTINCT i.userid, 
               CASE WHEN (i.settingKey || '#~#' || i.settingvalue) = ALL (a_v)
                              THEN i.settingKey ||  '#~#' || 'Y'
                              ELSE i.settingKey ||  '#~#' || 'N' END
               AS MatchResult
        FROM public.sample_table i
        WHERE (i.settingKey || '#~#' || i.settingvalue) = ALL (a_v)
        GROUP BY i.userid, MatchResult) AS MAInfo
        
        GROUP BY MAInfo.userid
        HAVING COUNT(MAInfo.userid) >= 1;
    
    RETURN QUERY (....);
            
END;
$BODY$;

CREATE TYPE tt_sample_function AS 

(
    key character varying,
    value character varying
    )

Inputs are

SELECT public.sample_function(
    '[{"key":"devicetype", "value":"TestType"},{"key":"ostype", "value":"TestType"}]'
)

Any suggestion, why my ALL operator is not working. I mean its always giving false, it should match with all the array elements...

Note: ofcourse data is there in table.

I don't understand the CASE expression you are using. The WHERE clause already uses = ALL(...) so the ELSE part of the CASE expression will never be reached. — user330315
– user330315, Commented Jul 31, 2020 at 8:10

score 1 · Accepted Answer · 2020-07-31 10:10:30Z

1

You are over complicating things. You don't need the FOR loop or the array to do the comparison. You can do that all in a single statement. No need for an extra TYPE or generating an array.

The parameter to the function should be declared as jsonb as you clearly want to pass valid JSON there.

I don't understand what you are trying to achieve with the CASE expression. The WHERE clause only returns rows that match the first condition in the CASE, so the second one will never be reached.

I also don't understand why you have the CASE at all, as you discard the result of that in the outer query completely.

But keeping the original structure as close as possible, I think you can simplify this to a single CREATE TABLE AS statement and get rid of all the array processing.

CREATE OR REPLACE FUNCTION public.sample_function(p_settings jsonb)
  RETURNS TABLE (..... )
  LANGUAGE plpgsql
AS $BODY$
declare 
...
begin

  CREATE TEMP TABLE hold_userID AS 
  SELECT MAInfo.userid 
  FROM (
    -- the distinct is useless as the GROUP BY already does that
    SELECT i.userid, 
           CASE 
             -- this checks if the parameter contains the settings key/value from sample_table
             -- but the WHERE clause already makes sure of that???
             WHEN p_settings @> jsonb_build_object('key', i.settingKey, 'value', i.settingvalue) 
               THEN i.settingKey ||  '#~#' || 'Y'
             ELSE i.settingKey ||  '#~#' || 'N' 
           END AS MatchResult
    FROM public.sample_table i
    WHERE (i.settingKey, i.settingvalue) = IN (select t.element ->> 'key' as key,
                                                      t.element ->> 'value' as value
                                               from jsonb_array_elements(p_settings) as t(element))
    GROUP BY i.userid, MatchResult
  ) AS MAInfo
  GROUP BY MAInfo.userid
  HAVING COUNT(MAInfo.userid) >= 1;

  return query ...;
end;
$body$

If you want to check if certain users have all the settings passed to the function, you don't really need a CASE expression, just a proper having condition

So maybe you want this instead:

CREATE TEMP TABLE hold_userID AS 
SELECT i.userid, 
FROM public.sample_table i
WHERE (i.settingKey, i.settingvalue) = IN (select t.element ->> 'key' as key,
                                                  t.element ->> 'value' as value
                                           from jsonb_array_elements(p_settings) as t(element))
GROUP BY i.userid
HAVING COUNT(*) = jsonb_array_length(p_settings);

Or alternatively:

SELECT i.userid 
FROM (
   select userid, settingkey as key, settingvalue as value
   from public.sample_table 
) i
group by i.userid 
HAVING jsonb_object_agg(key, value) = p_settings

edited Jul 31, 2020 at 10:10

answered Jul 31, 2020 at 8:22

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

sys Over a year ago

ok, thanks but- 1. Complicating things because that is a part, requirements are very complex, cant achive by this. Please ignore the sql query. 2. Here IN will not work me, as I have to match every values ( key and value ), which is there in the input, thats why I was trying to compare with ALL. 3. Here ALL operator is working what I expected. With IN operator, I can achieve using a simple table structure , IN is not helpful here.

sys Over a year ago

Thank you very much ! I put the above idea into my all-complex-never-ending solution. It worked perfectly, solves a part of my problem. Thanks again.

user330315 Over a year ago

"as I have to match every values" - which is exactly what the last query with count(*) = jsonb_array_length() does. The CASE expression will not achieve that. I have also added another way to check for "all" settings

Collectives™ on Stack Overflow

PostgreSQL - ALL ( array ) Operator - Suggestion

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related