Is it possible to create a view for a subquery referring the main query?

If have a query that uses a subquery. Basically it is like this:

SELECT A.name, A.pk 
       array_to_string(array(SELECT B.name FROM b WHERE B.relA  = A.pk ),', ')    
FROM A;

Basically it makes a column in A from a to-many relationship to B. (In my case A is a list of items and B contains tags related to that items. The query makes a column with a list of tags for each row in A.)

Since the real world query is more complex and I need the subquery more than one time, I want to make a view from the subquery (DRY). This is not possible, because A.pk is only known, if the subquery is a subquery in a main query that fetches from A. It is not known if the subquery stands alone. So I cannot create a view from the stand-alone version:

CREATE VIEW bview AS SELECT B.b FROM B WHERE B.relA=A.pk;

gives me the expected:

ERROR:  missing FROM-clause entry for table "A"

Is there a way to define akin of "incomplete view", that is not executed itself, but in a main query completing the subquery without using functions?

Edit: The WHERE clause inside the subquery cannot be replaced with a JOIN clause, because it takes the A.pk from the outer query.

Solution

You can create a simple view without referring to table A and then use that as a row source in various parts of your complex query:

CREATE VIEW bview AS
    SELECT relA, string_agg(name, ', ') AS tags
    FROM b
    GROUP BY relA;

This may seem inefficient because if you run the view like this without qualification, then all tags for all relA are concatenated. However, when you use the view in a larger query with qualifications, then the view is only evaluated for those relA values that are asked for in the outer query. This is because the view is "merged" with the outer query by the planner.

So you end up with:

SELECT name, pk, tags
FROM A
JOIN bview ON relA = pk;