current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have built a series of views in a PostgreSQL database that includes a couple of array columns. The view definition is as follows:

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

Essentially what I want to do is an iLike on the 'author' column to determine if a specific user id exists in that array. Obviously I can't use iLike on that datatype so I need to find another approach.

Here is an example of data in the 'authors' array:

{"(2373,t,f,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,t,Admin,A,A,A,0,\"\")","(2374,t,f,f,\"2011-08-01 11:57:40.706617\",/Pubs/pubs_edit_article.php,\"2011-08-09 15:36:29.285428\",000128343,A00592,Author,2,John,D.,Doe,\"\",234567890,t,IT,A,A,A,0,\"\")","(2381,t,f,f,\"2011-08-09 14:45:14.870418\",000128343,\"2011-08-09 15:36:29.28854\",000128343,A00592,Author,3,Jane,E,Doe,\"\",345678901,t,Admin,A,A,A,,\"\")","(2383,t,f,f,\"2011-08-09 15:35:11.845283\",567890123,\"2011-08-09 15:36:29.291388\",000128343,A00592,Author,4,Test,T,Testerton,\"\",TestTesterton,f,N/A,A,A,A,,\"\")"}

What I want to be able to do is a query the view and find out if the string '123456789' (that is the user id assigned to Nicholas Kreidberg in the array) exists in the array. I don't care which user it is assigned to or where it appears in the array, all I need to know is if '123456789' shows up anywhere in the array.

Once I know how to write a query that determines if the condition above is true then my application will simply execute that query and if rows are returned it will know that the user id passed to the query is an author for that publication and proceed accordingly.

Thanks in advance for any insight that can be provided on this topic.

share|improve this question
4  
Is there any reason why you cannot use a proper database structure? I.e. a separate table for authors and in case it's a m:n relation a table mapping articles to authors. –  ThiefMaster♦ Sep 3 '11 at 0:31
    
@ThiefMaster Arrays are provided by postgres and it's a reasonable question to ask how to search within one. –  Dana the Sane Sep 3 '11 at 2:47
    
@Dana, it's still an anti-pattern (no normalization, no index possible, joins are a pain, searching is a pain and they're uber slow). –  Johan Sep 3 '11 at 4:27
    
Yes, arrays are fine for some things and asking is fine. If someone uses it for a case where a separate table would make more sense (e.g. when accessing it via SQL instead of just retrieving the whole thing) asking why he doesn't do that is even finer. –  ThiefMaster♦ Sep 3 '11 at 7:18
    
@ThiefMaster: There is an authors table and all data from it is pulled into the view in the array column. The flow for articles is like this: a unique pubid (and a ton of other data) is stored in the articles table. One pubid can have many authors which are stored in an authors table. One pubid can also have many statuses (as it goes through the various stages of being published) so it can show up multiple times in the status table. The idea behind the view was to get all of that data (from articles, authors and status) into one spot for easier/faster queries and to avoid using joins. –  Nicholas Kreidberg Sep 6 '11 at 16:58
add comment

1 Answer

up vote 5 down vote accepted

Might this:

select ... 
  from ... 
 where ... 
       and array_to_string(authors, ', ') like '%123456789%';`

do the trick?

Otherwise, there is the unnest function...

The "Array Functions and Operators" chapter has more details.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.