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是该发布的作者并相应地继续.
提前感谢您就此主题提供的任何见解.
select ...
from ...
where ...
and array_to_string(authors,',') like '%123456789%';`
这个伎俩？
否则,有不必要的功能……
“Array Functions and Operators”章节有更多细节.
