sql - Alternatives to WITH .. AS .. clause in PostgreSQL -

i have several big queries of following type (simplified clarity).

create function myfunction()   returns void $$ begin  ... t (   total                 total,   total * 100 / total   total_percent,   total / people.count  total_per_person,   part1                 part1,   part1 * 100 / total   part1_percent,   part1 / people.count  part1_per_person,   part2                 part2,   part2 * 100 / total   part2_percent,   part2 / people.count  part2_per_person,   ...   (     select     total.amount total     part1.amount part1     part2.amount part2     ...     people.amount people     (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 another_table  -- << need replace "return query"         select .., total             t   union select .., total_percent     t   union select .., total_per_person  t   union select .., part1             t   union select .., part1_percent     t   union select .., part1_per_person  t   union select .., part2             t   union select .., part2_percent     t   union select .., part2_per_person  t   ...  ... $$ language plpgsql; 

the reason big columns derived others. query designed minimize repetition in pulling data , aggregating minimize run time (as takes 10 secs query run since mytable has little more 4 million rows). 15 columns inserted in another_table combined union operator.

with .. .. clause had worked scenario. now, refactoring program, have hand generated data sets function post processing (instead of inserting another_table).

so, had replace insert another_table return query, with .. .. did not that.

in other words, here updated function trying reach @ (which not work - interpreter not expecting return query after with .. as block):

create function myfunction()   returns setof data -- << returning data set $$ begin  ... t (   --same query   ) r )  return query  -- << line changed     -- same select here ... $$ language plpgsql; 

now question is, alternatives with .. ..? so, can possibly use return query it. plan try using temp tables, still curious how can rewrite query written with .. ....

the query in question has couple of obvious nonsense parts. since have been executing before, assume these artifacts manual simplification?
like: total * 100 / total pointless since burns down 100.
or: joins without join condition, plain syntax errors.

that aside, return query not sql plpgsql command:

• how return result of select inside function in postgresql?

either neglected mention using function or do statement plpgsql code, or trying use invalid syntax sql.

in plpgsql, works (obvious syntax errors aside) if put return query before sql query, cte (common table expression) - that's canonical name with clause - being part of sql statement:

return query        -- plpgsql command  t ( ... )  -- here starts sql query select .., total                 t union select .., total_percent   t union select.., total_per_person t -- etc. 

while being @ it, last part wrong. pretty sure want union all, not union fold duplicates in result.

better yet, use smart technique values expression in lateral join "counter-pivot" long rows:

... select t1.*   t, lateral (    values        (.., t.total)   -- whatever may hiding behind ".."      , (.., t.total_percent)      , (.., t.total_per_person)      , (.., t.part1)      , (.., t.part1_percent)        -- etc.    ) t1 ("name_for ..", total); 

should substantially shorter , cheaper. credits idea @andriy in related answer on dba.se

• select distinct on multiple columns