create view articles_view as 
  (select articles.*,array(select row(people.*)::people 
     from people 
     where articles.spubid=people.spubid and 
       people.stype='Author' and 
       bactive='t' 
     order by people.iorder) as authors,array(select row(people.*)::people 
     from people 
     where articles.spubid=people.spubid and 
       people.stype='Editor' and 
       bactive='t' 
     order by people.iorder) as editors,array(select row(people.*)::people 
     from people 
     where articles.spubid=people.spubid and 
       people.stype='Reviewer' and 
       bactive='t'
     order by people.iorder) as reviewers,array(select row(status.*)::status 
     from status 
     where articles.spubid=status.spubid and 
       bactive='t') as status
  from articles 
  where articles.bactive='t');

基本上我想做的是在’author’列上的iLike,以确定该数组中是否存在特定的用户ID.显然我不能在该数据类型上使用iLike,所以我需要找到另一种方法.

以下是’authors’数组中的数据示例：

{“(2373,t,f,\”2011-08-01
11:57:40.696496\”,/Pubs/pubs_edit_article.PHP,\”2011-08-09
15:36:29.281833\”,000128343,A00592,Author,1,Nicholas,K.,Kreidberg,\”\”,123456789,Admin,A,\”\”)”,”(2374,\”2011-08-01
11:57:40.706617\”,\”2011-08-09
15:36:29.285428\”,2,John,D.,Doe,234567890,IT,”(2381,\”2011-08-09
14:45:14.870418\”,\”2011-08-09
15:36:29.28854\”,3,Jane,E,345678901,”(2383,\”2011-08-09
15:35:11.845283\”,567890123,\”2011-08-09
15:36:29.291388\”,4,Test,T,Testerton,TestTesterton,N/A,\”\”)”}

我想要做的是查询视图并找出数组中是否存在字符串’123456789′(即数组中分配给Nicholas Kreidberg的用户ID).我不关心它被分配给哪个用户或它在数组中出现的位置,我需要知道的是,如果’123456789’出现在数组中的任何位置.

一旦我知道如何编写一个确定上述条件是否为真的查询,那么我的应用程序将只执行该查询,如果返回行,它将知道传递给查询的用户id是该发布的作者并相应地继续.

提前感谢您就此主题提供的任何见解.