CODEWITHSUNDEEP
postgresql
user10176607
user10176607

Reputation:

Converting string to array and using in where clause

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;

Upvotes: 2

Views: 5332

Answers (1)

user330315
user330315

Reputation:

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);

Upvotes: 1

Related Questions