arrays - PostgreSQL 的 id 匹配列表

Question

我正在使用一些过滤器扩展我对 media 项目的选择，其中之一是 category。媒体和类别之间的关系是多对多的，这意味着我有一个关系表 media_categories。我正在以 integer[] 的形式发送带有类别 ID 的类别过滤器。如何匹配两个数组以获得公共(public)元素？

这是我的存储过程:(见代码中的注释)

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;

谢谢

Answer 1

嗯，我猜你正在寻找 postgre 数组函数<@(包含于)

http://www.postgresql.org/docs/9.2/static/functions-array.html

条件必须类似于:

AND (SELECT ARRAY (SELECT category_id FROM mediabase.media_categories WHERE media_id = media.id)) <@ categoryIds