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

Postgresql – How to make a condition in where clause as void

I have a function in Postgresql where we have a parameter uuid[]. I basically have 2 requirements.

  1. uuid[] I would like to make it as optional
  2. In the where clause if uuid[] is null or not passed then the condition I would like to make it as void something similar to COALESCE.

I tried the below function for my requirement . For setting it as optional I have set it as default null and in the where clause used COALESCE. However it throws error saying
syntax error at or near "ANY"

CREATE OR REPLACE FUNCTION spgetuserinvitations(    
    p_useremail character varying,
    p_homeids uuid[] default null)
    RETURNS TABLE(invitationid uuid) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
#variable_conflict use_column

<<fnp>>
DECLARE
    v_exmsg VARCHAR(1000);  
BEGIN  
    
    RETURN QUERY
SELECT
  *
FROM table  
WHERE (useremail) = (p_useremail)
    AND invh.homeid = COALESCE(ANY(p_homeIds),invh.homeid)    

  EXCEPTION WHEN OTHERS THEN
                    GET STACKED DIAGNOSTICS fnp.v_errorstate = RETURNED_SQLSTATE,
                        fnp.v_errormsg = MESSAGE_TEXT;
                    fnp.v_exmsg := COALESCE(fnp.v_errormsg, 'Unknown Error');
                    RAISE EXCEPTION 'Error %, severity %, state % was raised. Message: %.', '50000', 14, -1, fnp.v_exmsg USING ERRCODE = fnp.v_errorstate;
END fnp;
$BODY$;
Tags:

2

Answers


  1. 0

    How about:

    AND (CASE WHEN p_homeIds IS NULL THEN true ELSE invh.homeid IN p_homeIds END)
    Login or Signup to reply.
  2. 0

    Assuming that p_homeIds holds a list of identifiers that you normally want to filter down to, but if the function call skips p_homeIds entirely or passes a null there, you want to disable the filter: demo

    WHERE (useremail) = (p_useremail)
AND (p_homeIds IS NULL OR invh.homeid=ANY(p_homeIds) );

    As to why this happened, the doc explains the expected syntax for ANY:

    expression operator ANY (array expression)

    It has to be directly preceded by an operator which was missing in your case. A section earlier, there’s also an ANY with a subquery instead of the array but it still expects to be directly preceded by an operator.

    The idea suggested by the other answer is valid, but again the syntax isn’t: IN works with a subquery (where you could unnest an array), or with a literal list, but not with a plain array expression directly.

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

Back To Top
Search