Use postgres CTE in multiple queries

user_779159 0
Viewed 2999

I can use a CTE in a single query like this

with mycte as (...)
  insert into table1 (col1) select col1 from mycte where col1 in
    (select col1 from mycte)

But what if I want to use mycte in multiple queries? How can I make something like this work?

with mycte as (...)
  insert into table1 (col1) select col1 from mycte where col1 in
    (select col1 from mycte),
  insert into table2 (col1) select col1 from mycte where col1 in
    (select col1 from mycte)
common-table-expression postgresql sql
2 Answers

For multiple inserts, you can put them into the same query:

with mycte as (...),
     i1 as (
      insert into table1 (col1)
          select col1
          from mycte
          where col1 in (select col1 from mycte)
          returning *
     )
insert into table2 (col1)
    select col1
    from mycte
    where col1 in (select col1 from mycte);
user_11440350

A CTE is an ad-hoc view. If you want a permanent view that you can use in multiple queries, then use CREATE VIEW instead.

user_22707620
Related