I have several big queries of the following type (simplified for clarity).
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 -- << 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;
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 mytable
has little more than 4 million rows). All 15 columns are inserted in another_table combined with union operator.
with .. as ..
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).
So, I had to replace the insert into another_table
with return query
, but WITH .. AS ..
did not like that.
In other words, Here is the updated function I am trying to reach at (which does not work - interpreter is not expecting return query
after with .. as
block):
create function myfunction()
returns setof data -- << now returning a data set
as $$
begin
...
with t as (
--SAME QUERY
) r
)
return query -- << line that is changed
-- SAME SELECT HERE
...
$$ language plpgsql;
Now my question is, what are the alternatives to WITH .. AS ..
? So, I can possibly use return query
with it. I plan to try using temp tables, but I am still curious how can I rewrite a query written with with .. as ...
.
The query in the question has a couple of obvious nonsense parts. Since it's been executed before, I assume artifacts from manual simplification?
Like:
would be pointless since it burns down to justtotal * 100 / total
100
.Or: joins without join condition, which are plain syntax errors.
That aside,
RETURN QUERY
is not an SQL but a PL/pgSQL command:Either you neglected to mention the PL/pgSQL code block, or you are trying to use invalid syntax for SQL.
In PL/pgSQL, this works (obvious syntax errors aside) if you put
RETURN QUERY
before the SQL query, the CTE (Common Table Expression) - that's the canonical name for aWITH
clause - being part of the SQL statement:While being at it, that last part is most probably wrong. I am pretty sure you want
UNION ALL
, notUNION
which would fold any duplicates in the result.Better yet, use this smart technique with a
VALUES
expression in aLATERAL
join to "unpivot" your long rows:Should be substantially shorter and cheaper. Credits for the idea goes to @Andriy in this related answer on dba.SE