How to check 'any or limited' in the WHERE clause of a query?

Question

I have this plpgsql function:

CREATE OR REPLACE FUNCTION func_example(
    IN  check_ids      INT [],
    OUT recs           REFCURSOR,
    OUT po_result_code TEXT
) RETURNS RECORD LANGUAGE plpgsql AS $$
BEGIN
    OPEN recs FOR
    SELECT *
    FROM my_table t
      JOIN my_another_table tt on tt.tid = t.id
    WHERE t.enabled = TRUE
          AND tt.some = 1
          AND (
              check_ids IS NULL OR check_ids.count = 0 /* <-- problem here */
              OR t.id = ANY (check_ids)
          );

    po_result_code := 0;
    RETURN;
END;
$$;

Calling it results in the error message:

Error: [42P01] ERROR: missing FROM-clause entry for table "check_ids"  
SQL state: 42P01

How to check 'argument is null or value in argument'?

Some sample data:

CREATE TABLE my_table (
    id      INT,
    enabled BOOLEAN DEFAULT TRUE
);
CREATE TABLE my_another_table (
    tid    INT,
    "some" INT DEFAULT 1,
    CONSTRAINT t_another_to_my_fk FOREIGN KEY (tid) REFERENCES my_table (id)
);
INSERT INTO my_table (id, enabled) VALUES (1, TRUE);
INSERT INTO my_another_table (tid, "some") VALUES (1, 1);

Answer 1

To also let NULL and empty array pass, replace:

(t.id = ANY (check_ids) OR check_ids IS NULL OR check_ids.count = 0)  -- illegal syntax

with the single expression:

(t.id = ANY (check_ids) OR (check_ids = '{}') IS NOT FALSE)

This would work, too, but a bit slower:

(t.id = ANY (check_ids) OR check_ids IS NULL OR check_ids = '{}')

Closely related answer for string types with detailed explanation:

• Best way to check for "empty or null value"

However, your actual question asks for something different:

How to check 'argument is null or value in argument'?

That would burn down to simply:

(t.id = ANY (check_ids) OR check_ids IS NULL)

All of this is in the realm of SQL and unrelated to PL/pgSQL.

Answer 2

Replace check_ids.count with array_length(check_ids, 1).