CODEWITHSUNDEEP
arrayspostgresqlwhere-clauseplpgsql
FORTRAN
FORTRAN

Reputation: 637

Postgres function with text array and select where in query

I need to create a function like this (scaled down to a minimum) where I send an array of strings that should be matched. But I cant make the query to work.

create or replace function bar(x text[]) returns table (c bigint) language plpgsql as $$
begin
    return query select count(1) as counter from my_table where my_field in (x);
end;$$;

and call it like this

select * from bar(ARRAY ['a','b']);

I could try to let the parameter x be a single text string and then use something like

return query execute 'select ... where myfield in ('||x||')';

So how would I make it work with the parameter as an array? would that be better or worse compared to let the parameter be a string?

Upvotes: 2

Views: 8102

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

Yes, an array is the cleaner form. String matching invites corner cases where the separator is part of the string.

To find strings that match any of the given patterns, use the ANY construct:

CREATE OR REPLACE FUNCTION bar(x text[])
  RETURNS bigint
  LANGUAGE sql AS
$func$
SELECT count(*)   -- alias wouldn't be visible outside function
FROM   my_table 
WHERE  my_field = ANY(x);
$func$;

count(*) is slightly faster than count(1). Same result in this query.

Using a plain SQL function (instead of PL/pgSQL). Either has its pros and cons.

Upvotes: 5

bereal
bereal

Reputation: 34281

That's fixed with the help of unnest that converts an array to a set (btw, the function doesn't have to be plpgsql):

CREATE OR REPLACE FUNCTION bar(x text[]) RETURNS BIGINT LANGUAGE sql AS $$
    SELECT count(1) AS counter FROM my_table 
        WHERE my_field IN (SELECT * FROM unnest(x));
$$;

Upvotes: 2

FORTRAN
FORTRAN

Reputation: 637

The problem with using the array seems to be fixed by using

return query select count(1) as counter from my_table where my_field in (array_to_string(x,','));

The point of effiency still remains unsolved.

Upvotes: 0

Related Questions