Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise

I have a postgres storage procedure:

CREATE OR REPLACE FUNCTION get_stats(
    _start_date timestamp with time zone,
    _stop_date timestamp with time zone,
    id_clients integer[],
    OUT date timestamp with time zone,
    OUT profit,
    OUT cost
)
RETURNS SETOF record
LANGUAGE plpgsql
AS $$
DECLARE
    query varchar := '';
BEGIN
... --lof of code
IF id_clients IS NOT NULL THEN
    query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;
... --other code
END;
$$;

So if I run query something like this:

SELECT * FROM get_stats('2014-07-01 00:00:00Etc/GMT-3', '2014-08-06 23:59:59Etc/GMT-3', '{}');

Generated query has this condition:

"... AND id = ANY('{}')..."

But if an array is empty this condition should not be represented in query.

How can I check if the array of clients is not empty? Thanks.

UPD: Also I've tried two variants:

IF ARRAY_UPPER(id_clients) IS NOT NULL THEN
    query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;

And

IF ARRAY_LENGTH(id_clients) THEN
    query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;

In both cases I got this error: ARRAY_UPPER(ARRAY_LENGTH) doesn't exists ;

share|improve this question
    
up vote 3 down vote accepted

array_length() requires two parameters, the second being the dimension of the array:

array_length(_term_id_clients, 1) > 0
share|improve this answer
    
Thanks @Erwin Brandstetter, I'm really missed second parameter for array_length. – joni jones Aug 6 '14 at 9:03

if for some reason you don't want to supply the dimension of the array, cardinality will return 0 for an empty array:

From the docs:

cardinality(anyarray) returns the total number of elements in the array, or 0 if the array is empty

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.