title: PostgreSQL match list of id's
body: <p>I'm expanding my select of <code>media</code> items with some filters which one of them is <code>category</code>. The relation between media and category is many-to-many which meens that I have a relation table <code>media_categories</code>. I'm sending a categories filter in form of <code>integer[]</code> with category id's.
  How do I match two arrays to get the common elements? </p>
  
  <p>Here is my stored procedure:
  (see comment in the code)</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION mediabase.select_media(sysenvironment character varying, statusid integer, wildcard character varying, categoryIds integer[])
    RETURNS refcursor AS
  $BODY$
      DECLARE ref refcursor;
  
      BEGIN
        OPEN ref FOR 
  
      SELECT 
          media.id, 
          media.title, 
          media.unique_filename, 
          media.owner_id, 
          media.status_id, 
          media.location_name_id, 
          media.upload_user_id, 
          media.upload_ip, 
          media.metadata_id, 
          media.type_id, 
          media.description, 
          media.system_environment, 
          media.upload_date, 
          media.gps_location, 
          media.language_id, 
          (SELECT ARRAY (SELECT publication_id FROM mediabase.media_publications WHERE media_id = media.id)) as publication_ids,
          media.limitations, 
          (SELECT ARRAY (SELECT category_id FROM mediabase.media_categories WHERE media_id = media.id)) as category_ids,
          (SELECT ARRAY (SELECT keyword_id FROM mediabase.media_keywords WHERE media_id = media.id)) as keyword_ids,
          media.credits,
          metadata.width, 
          metadata.height, 
          metadata.equipment, 
          metadata.copyright, 
          metadata.creation_time, 
          metadata.file_format, 
          metadata.resolution, 
          metadata.resolution_unit, 
          metadata.gps_longitude, 
          metadata.gps_latitude, 
          metadata.artist, 
          metadata.color_space, 
          metadata.gps_altitude, 
          metadata.software_used, 
          metadata.user_comment
      FROM 
          mediabase.media, 
          mediabase.metadata
      WHERE media.metadata_id = metadata.id
      AND (media.status_Id = statusId OR statusId = -1)
      AND media.system_environment = sysEnvironment
      AND (lower(media.title) LIKE lower('%'||lower(wildcard)||'%') OR lower(media.description) LIKE lower('%'||lower(wildcard)||'%') OR lower(metadata.artist) LIKE lower('%'||lower(wildcard)||'%'))
      -- Problem start
      -- in the following line I'm trying to make the match with no success
      AND (SELECT ARRAY (SELECT category_id FROM mediabase.media_categories WHERE media_id = media.id)) IN (categoryIds) 
      -- Problem end
      ORDER BY media.upload_date DESC;
  
        RETURN ref;                       
      END;
      $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
  </code></pre>
  
  <p>Thanks</p>