2

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?

FORTRAN
  • 567
  • 1
  • 6
  • 13
  • Seems it was easy to fix the syntax when using the array by replacing x with array_to_string(x,',') – FORTRAN Oct 08 '14 at 08:41

3 Answers3

5

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
2

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));
$$;
bereal
  • 32,519
  • 6
  • 58
  • 104
  • When trying to create your function I get ERROR: column "x" does not exist LINE 3: WHERE my_field IN (SELECT * FROM unnest(x)); – FORTRAN Oct 09 '14 at 07:47
  • Did you copy-paste it exactly as it is? Just re-tried successfully. Could your real function have a different arg name for `x`? – bereal Oct 09 '14 at 07:50
0

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.

FORTRAN
  • 567
  • 1
  • 6
  • 13
  • @bereal, unless I'm misstaken, it works, but please prove that I'm wrong. – FORTRAN Oct 09 '14 at 07:32
  • Having two entries `'foo'` and `'bar'`, I called `select * from bar(ARRAY['foo', 'bar'])` and got `0`. Actually, `array_to_string(x, ',')` returns `'foo,bar'` for this case. – bereal Oct 09 '14 at 07:47