joni jones joni jones - 7 months ago 83
SQL Question

How to check if an array is empty in Postgres

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
;

Answer

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

array_length(_term_id_clients, 1) > 0