title: refer to array from SELECT clause in WHERE clause
body: <p>I have the following two tables:</p>
  
  <pre><code>create table person
  (
      identifier  integer     not null,
      name        text        not null,
      age         integer     not null,
  
      primary key(identifier)
  );
  
  create table agenda
  (
      identifier  integer     not null,
      name        text        not null,
  
      primary key(identifier)
  );
  </code></pre>
  
  <p>They are joined with the following table:</p>
  
  <pre><code>create table person_agenda
  (
      person_identifier   integer not null,
      agenda_identifier   integer not null,
  
      primary key(person_identifier, agenda_identifier),
      foreign key(person_identifier) references person(identifier),
      foreign key(agenda_identifier) references agenda(identifier)
  );
  </code></pre>
  
  <p>I am trying to refer to an array, as definied in the <code>SELECT</code> clause, in the <code>WHERE</code> clause.</p>
  
  <p>The following works:</p>
  
  <pre><code>select identifier, name, array(select identifier from agenda a, person_agenda pa where person_identifier = p.identifier and identifier = agenda_identifier and name = '...') as r
  from person p;
  </code></pre>
  
  <p>This does not:</p>
  
  <pre><code>select identifier, name, array(select identifier from agenda a, person_agenda pa where person_identifier = p.identifier and identifier = agenda_identifier and name = '...') as r
  from person p
  where array_length(r, 1) &gt;= 1;
  </code></pre>
  
  <p>It says that <code>r</code> is not a known column. How can I refer to this array in the <code>WHERE</code> clause?</p>
  
  <p>The purpose of my second query is to:</p>
  
  <ul>
  <li>omit persons without agendas (by filtering on <code>array_length()</code> >= 1)</li>
  <li>get all agenda identifiers, so I can fetch their information in a subsequent query without having to filter again (on field <code>agenda.name</code> in my example above) (by projecting the array in the <code>SELECT</code> clause)</li>
  </ul>
  
  <p>The first bullet can be done with a simple join. But, for the first bullet in combination with the second bullet, I need some kind of aggregation on the agenda identifiers. I thought arrays would be useful for this.</p>
  
  <p><strong>edit</strong></p>
  
  <p>According to user Saba, this is not possible. Thanks for your feedback.</p>
  
  <p>Is the following query a good alternative?</p>
  
  <pre><code>select   person.identifier, person.name, array_agg(agenda.identifier)
  from     person, person_agenda, agenda
  where    person.identifier = person_identifier and
           agenda.identifier = agenda_identifier and
           agenda.name = '...'
  group by person.identifier;
  </code></pre>