Postgres sql in return all when array empty without dynamic sql

Question

I came into a problem in postgres sql function, I need to return all records when the array of integer is empty.

For now I am using dynamic sql to achieve it with if statement checking if the array is empty so don't concat in statement in the query. I want to know if there is another way to achieve it without dynamic sql?

eg using dynamic sql:

CREATE OR REPLACE FUNCTION testme() RETURNS TABLE(Title text) LANGUAGE plpgsql AS $$
DECLARE
one int[];
BEGIN
one:= '{}';

RAISE NOTICE 'value:%', array_length(one,1);
if array_length(one,1) > 0 THEN
RETURN QUERY EXECUTE 'SELECT  "Title" from "Tickets" where "TicketID"  
IN(SELECT(UNNEST($1)));'  USING one;
ELSE
RETURN QUERY EXECUTE 'SELECT "Title"  from "Tickets"';

END IF;
END
$$;
SELECT testme();

Clodoaldo Neto · Accepted Answer · 2017-04-17 16:42:27Z

5

select  "Title"
from "Tickets"
where
    "TicketID" = any (one)
    or
    cardinality(one) = 0;

cardinality returns 0 for an empty array whereas array_length returns null

answered Apr 17, 2017 at 16:42

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

3 Comments

Richard Huxton Over a year ago

Hmm - never noticed that about array_length before. Presumably to do with not knowing how many dimensions any particular array could have. Thanks Clodoaldo.

duduhayo Over a year ago

thanks it worked!!! btw is it true that any have bad performance vs in i showed in my answer? also the answer works with IN(SELECT(UNNEST(one)))

Clodoaldo Neto Over a year ago

@duduhayo They should behave the same.

Collectives™ on Stack Overflow

Postgres sql in return all when array empty without dynamic sql

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