problem creating PLpgSQL function which accepts ARRAY as INPUT and returns SETOF RECORD from the table

Question

I'm trying to create function which will accept ARRAY as INPUT and then return SETOF RECORD for each of the parameter in ARRAY. I have table country_regions which consists of 3 Columns: id int, region_name TEXT, country_name TEXT;

My Functions code looks like this:

CREATE OR REPLACE FUNCTION search1(TEXT[])
RETURNS SETOF RECORD AS $$

DECLARE x RECORD;

BEGIN 
    FOR x IN
        SELECT *
        FROM company_regions
        WHERE country_name = $1::TEXT
        LOOP
        RETURN NEXT x;
        END LOOP;
END; $$
LANGUAGE plpgSQL;

This Function was created successfully, but when I try to call the function like this:

SELECT * FROM search1(ARRAY ['usa', 'canada']) AS search1(id int, region_name TEXT, country_name text)

it returns table with 0 rows in it.

Can someone tell me what am I doing wrong? I'm completely new to SQL, tried to find answer in other post but I still could not figure out the problem.

Answer 1

You try to compare text value versus text[].

CREATE OR REPLACE FUNCTION search1(text[])
RETURNS SETOF company_regions AS $$
BEGIN
  RETURN QUERY SELECT * FROM company_regions
                 WHERE country_name = ANY($1);
END
$$ LANGUAGE plpgsql STABLE

Attention - functions like this are black box for optimizer. Usually is not too good (from performance perspective) using functions like envelops of one SQL statement. In complex query it can block some optimizations (Mainly if you forget to set correct flag of function - in this case STABLE).

Answer 2

Simply add autocomplete="off" with form

More details: https://developer.mozilla.org/en-US/docs/Web/Security/Securing_your_site/Turning_off_form_autocompletion