title: Alternatives to WITH .. AS .. clause in PostgreSQL
body: <p>I have several big queries of the following type (simplified for clarity). </p>
  
  <pre><code>create function myfunction()
    returns void
  as $$
  begin
  
  ...
  with t as (
    total                 as total,
    total * 100 / total   as total_percent,
    total / people.count  as total_per_person,
    part1                 as part1,
    part1 * 100 / total   as part1_percent,
    part1 / people.count  as part1_per_person,
    part2                 as part2,
    part2 * 100 / total   as part2_percent,
    part2 / people.count  as part2_per_person,
    ...
    from (
      select
      total.amount as total
      part1.amount as part1
      part2.amount as part2
      ...
      people.amount as people
      from (select ...from mytable..) total
      left join (select ...from mytable..) part1 on ...
      left join (select ...from mytable..) part2 on ...
      ...
      left join (select ...from mytable..) people on ...
    ) r
  )
  
  insert into another_table  -- &lt;&lt; NOW I NEED TO REPLACE THIS WITH "RETURN QUERY"
          select .., total             from t
    union select .., total_percent     from t
    union select .., total_per_person  from t
    union select .., part1             from t
    union select .., part1_percent     from t
    union select .., part1_per_person  from t
    union select .., part2             from t
    union select .., part2_percent     from t
    union select .., part2_per_person  from t
    ...
  
  ...
  $$ language plpgsql;
  </code></pre>
  
  <p>The reason it is so big is that most columns are derived from others. Query was designed to minimize repetition in pulling data and aggregating so to minimize run time (as it is it takes about 10 secs for this query to run since <code>mytable</code> has little more than 4 million rows). All 15 columns are inserted in another_table combined with union operator.</p>
  
  <p><code>with .. as ..</code> clause had worked perfectly for this scenario. But now, refactoring the program, I have to hand the generated data sets to another function for post processing (instead of inserting into another_table). </p>
  
  <p>So, I had to replace the <code>insert into another_table</code> with <code>return query</code>, but <code>WITH .. AS ..</code> did not like that. </p>
  
  <p>In other words, Here is the updated function I am trying to reach at (which does not work - interpreter is not expecting <code>return query</code> after <code>with .. as</code> block): </p>
  
  <pre><code>create function myfunction()
    returns setof data -- &lt;&lt; now returning a data set
  as $$
  begin
  
  ...
  with t as (
    --SAME QUERY
    ) r
  )
  
  return query  -- &lt;&lt; line that is changed
      -- SAME SELECT HERE
  ...
  $$ language plpgsql;
  </code></pre>
  
  <p>Now my question is, what are the alternatives to <code>WITH .. AS ..</code>? So, I can possibly use <code>return query</code> with it. I plan to try using temp tables, but I am still curious how can I rewrite a query written with <code>with .. as ...</code>.</p>