Alternatives to WITH .. AS .. clause in PostgreSQL

Question

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 ....

You've shown a thing that works. Please post the thing that does not work. Perhaps with a very small sample data set as well? — Karl Kieninger, Jun 20 '15 at 00:10
The code that does NOT work is same as above except one line, which I pointed out above and marked more distinctively now as THIS IS WHAT DOES NOT WORK. I did not want to copy and paste the whole for a few reasons. — mehmet, Jun 20 '15 at 00:22
And you are wrapping this in a function declared how? When you say "did not like" what does that mean exactly? Did you get a specific error? — Karl Kieninger, Jun 20 '15 at 00:35
Yes, this was in a pl/pgsql function. I added those details. I am sorry, I was trying to hide the -thought to be- unnecessary details of implementation so to get the attention on rewriting the `with .. as` query. — mehmet, Jun 20 '15 at 22:11

Erwin Brandstetter · Accepted Answer · 2022-03-28T00:18:45.710

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: ~~total * 100 / total~~ would be pointless since it burns down to just 100.
Or: joins without join condition, which are plain syntax errors.

That aside, RETURN QUERY is not an SQL but a PL/pgSQL command:

How to return result of a SELECT inside a function in PostgreSQL?

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 a WITH clause - being part of the SQL statement:

RETURN QUERY        -- plpgsql command
WITH  t AS ( ... )  -- here starts the SQL query
SELECT .., total                 FROM t
UNION SELECT .., total_percent   FROM t
UNION SELECT.., total_per_person FROM t
-- etc.

While being at it, that last part is most probably wrong. I am pretty sure you want UNION ALL, not UNION which would fold any duplicates in the result.

Better yet, use this smart technique with a VALUES expression in a LATERAL join to "unpivot" your long rows:

...
SELECT t1.*
FROM   t, LATERAL (
   VALUES
       (.., t.total)   -- whatever you may be hiding behind ".."
     , (.., t.total_percent)
     , (.., t.total_per_person)
     , (.., t.part1)
     , (.., t.part1_percent)
       -- etc.
   ) t1 ("name_for ..", total);

Should be substantially shorter and cheaper. Credits for the idea goes to @Andriy in this related answer on dba.SE

SELECT DISTINCT on multiple columns

Right, artifacts from simplification. I kept total * 100 / total there since it was in pattern with others (better readability I think). This was in a pl/pgsql function (added above). You were right about `union`, I should have used `union all` insead, but the data was not supposed to have overlap anyways. Thanks for taking the time to answer. — mehmet, Jun 20 '15 at 22:17
Perfect. So I had to put `return query` above `with .. as`; this answers my question. I will look into your other suggestion. Thanks, — mehmet, Jun 20 '15 at 22:21

Alternatives to WITH .. AS .. clause in PostgreSQL

1 Answers1