current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
    
Seems it was easy to fix the syntax when using the array by replacing x with array_to_string(x,',') –  Gunnar Oct 8 '14 at 8:41

3 Answers 3

up vote 1 down vote

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));
$$;
share|improve this answer
    
When trying to create your function I get ERROR: column "x" does not exist LINE 3: WHERE my_field IN (SELECT * FROM unnest(x)); –  Gunnar Oct 9 '14 at 7: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 9 '14 at 7:50
up vote 1 down vote

Yes, an array is the cleaner form. String matching would leave corner cases where separators and patterns combined match ...

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 visible outside function
FROM   my_table 
WHERE  my_field = ANY(x);
$func$;

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

Note, I am using a plain SQL function (instead of plpgsql). Either has its pros and cons.

share|improve this answer
up vote 0 down vote

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.

share|improve this answer
    
Are you sure it works for x with more than one element? –  bereal Oct 8 '14 at 8:46
    
@bereal, unless I'm misstaken, it works, but please prove that I'm wrong. –  Gunnar Oct 9 '14 at 7: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 9 '14 at 7:47
    
I was mistaken! unnest worked better! Thanks! –  Gunnar Oct 9 '14 at 7:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.