postgresql: adding an array function to an existing query

[garlinto]

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

[garlinto]

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