Converting string to array and using in where clause

Question

I need to pass a string of integers that need to be converted to an array. This will be an array of primary keys that will used as a filter a table.

When trying to call the function with paramaters such as this: asset.get_asset_stores('example.com', '1,2,3'), I get the following error:

SQL Error [42883]: ERROR: function string_to_array(character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Below is the function:

DROP FUNCTION IF EXISTS asset.get_asset_stores;

CREATE OR REPLACE FUNCTION asset.get_asset_stores(
    IN _username VARCHAR(100),
    IN _asset_store_type_ids VARCHAR)


RETURNS TABLE(
 asset_store_id INT
,asset_store_name VARCHAR
,asset_store_desc VARCHAR
,asset_store_type_id INT
,asset_store_type VARCHAR
,site_id INT) 

AS $$

DECLARE 
i_user_id INT;
a_asset_store_type_ids INT[];

BEGIN

i_user_id = erbaccess.getUserIdFromUserName(_username);

a_asset_store_type_ids = STRING_TO_ARRAY(_asset_store_type_ids, ',')::INT[];

RETURN QUERY
    SELECT
        astores.asset_store_id
        ,astores.asset_store_name
        ,astores.asset_store_desc
        ,astores.asset_store_type_id
        ,astores.asset_store_type
        ,astores.site_id
    FROM asset.asset_stores AS astores
    INNER JOIN customer.sitesvsusers AS svu
        ON svu.siteid = astores.site_id
            AND svu.userid = i_user_id
    WHERE astores.asset_store_type_id IN (a_asset_store_type_ids);


 END;
 $$ LANGUAGE PLPGSQL SECURITY DEFINER;

Unrelated, but if you just want to encapsulate a query, a language sql is much more efficient — user330315
– user330315, Commented Oct 3, 2019 at 15:13

score 1 · Accepted Answer · 2019-10-03 14:56:41Z

1

As documented in the manual you need to provide the delimiter on which the string should be split, e.g. a ','

a_asset_store_type_ids := STRING_TO_ARRAY(_asset_store_type_ids, ',');

You can't use IN to check for elements in an array, you need to use the ANY operator:

WHERE astores.asset_store_type_id = ANY(a_asset_store_type_ids);

edited Oct 3, 2019 at 14:56

answered Oct 3, 2019 at 14:48

user330315

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

4 Comments

user10176607 Over a year ago

Thanks, I updated like you said and as also tried adding ::INT[].. in both cases I still get the following error now: SQL Error [42883]: ERROR: operator does not exist: integer = integer[] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Without changing the cast type, the following error comes up: SQL Error [42846]: ERROR: cannot cast type text[] to integer

user10176607 Over a year ago

removing cast typing and using WHERE astores.asset_store_type_id = ANY(a_asset_store_type_ids); worked...thank you!

user330315 Over a year ago

@kravb: it would be much better to define your function parameter as int[] rather than passing a string

user10176607 Over a year ago

this function will be called from Python and when we tried to pass integers as parameters, we seem to randomly get an error that function parameters don't match values passed with a hint to maybe explicitly type cast... Not sure why Postgres doesn't like getting integers from Python... we are using psycopg2

Collectives™ on Stack Overflow

Converting string to array and using in where clause

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related