In this section, we are going to talk about CTEs. This section will be split into three parts. Firstly, we will talk about the concept of CTEs; secondly, we will discuss how CTEs are implemented in PostgreSQL 12; and finally, we will explore some examples of how to use CTEs.
CTE concept
A CTE, or a common table expression, is a temporary result taken from a SQL statement. This statement can contain SELECT, INSERT, UPDATE, or DELETE instructions. The lifetime of a CTE is equal to the lifetime of the query. Here is an example of a CTE definition:
WITH cte_name (column_list) AS (
CTE_query_definition
)
statement;
If, for example, we wanted to create a temporary dataset with all the posts written by the author scotty, we would have to write this:
forumdb=# with posts_author_1 as
(select p.* from posts p
inner join users u on p.author=u.pk
where username='scotty')
select pk,title from posts_author_1;
pk | title
----+--------------
4 | Re:my orange
5 | my tomato
(2...