Postgresql: adding an array function to an existing query

Databases
1

I have have a query that returns rows from a blog database that can match a number of different supplied criteria. The query works as expected to return the data I require.

A typical example of the sql:


select cr.category_id
       , c.name as category_name
       , p.post_id
       , p.added
       , p.title
       , ts_headline(ts.title, ts.ts_query) as headline
       , ts.ts_query
       , ts.rank from (
              select post_id
                     , title
                     , url
                     , added
                     , ts_query
                     , ts_rank_cd(search_tsv, ts_query) AS rank
              from public.posts, plainto_tsquery('this is a test post') as ts_query
              where search_tsv @@ ts_query order by rank desc ) as ts

inner
     join public.category_relationships as cr
     on cr.post_id = ts.post_id

inner
     join public.categories as c
     on cr.category_id = c.category_id

inner
     join public.posts as p
     on cr.post_id = p.post_id

where cr.category_id = 9
  and
       p.added between '2010-12-01 00:00:00' and '2010-12-31 23:59:59'
  and
      p.post_id in (
          select post_id
          from public.tags
          where tag = 'tag1' or tag = 'tag2' or tag = 'tag3')

order by ts.rank, p.added DESC

While I can search for a blog post using the above based on tags as part of the search criteria, I do not include the tags in the returned data since I will get a row of data for a certain post_id for each tag that exists for that post_id.

What I would like to do is include the following as a subquery for returning a formatted string containing the tags for the relevant post:


select array_to_string(
       array(
              select tag from public.tags where post_id = p.post_id
       ), ', ')
      as tags

Of course, the error is obvious: the alias ‘p’ does not exist in the subquery scope. My dilemma is how do I incorporate this subquery into the larger query above and pull the p.post_id into the subquery? I’m sure I’m just being obtuse here, but any thoughts would be appreciated :smiley:

2

Actually, I discovered that I am using the wrong query with that particular subquery. Sorry to anyone who pondered this for me. While I would still like to be able to use that subquery with the original sql I posted (where the post_id may or may not be known), the query I needed to run on the database is below (the context of which the post_id is always known):


select p.*
      , cr.category_id
      , array_to_string(
             array(
                    select tag from public.tags where post_id = 8
             ),
             ', '
        ) as tags

from public.posts as p

inner
     join public.category_relationships as cr
     on cr.post_id = p.post_id

where
      p.enabled is true
and
      p.post_id = 8;

returns the data exactly as I need it. Thanks again to anyone who looked at this :stuck_out_tongue: