{"id":1413,"date":"2013-03-25T07:42:31","date_gmt":"2013-03-25T14:42:31","guid":{"rendered":"http:\/\/blog.light42.com\/wordpress\/?p=1413"},"modified":"2013-09-25T07:43:47","modified_gmt":"2013-09-25T14:43:47","slug":"postgresql-banter-on-with","status":"publish","type":"post","link":"http:\/\/blog.light42.com\/wordpress\/?p=1413","title":{"rendered":"PostgreSQL banter on &#8216;with&#8217;"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2012\/06\/PostgreSQL_logo.3colors.120x120.png\" alt=\"PostgreSQL_logo.3colors.120x120\" width=\"120\" height=\"120\" class=\"alignright size-full wp-image-575\" \/><br \/>\n<strong>RhodiumToad<\/strong>\twith foo(x) as (values (&#8216;blah blah blah&#8217;)) insert into wherever select x from foo;<br \/>\n<strong>merlinm<\/strong>\tor, with vars as (select &#8216;a&#8217; as a, 2 as b) insert into foo values (select a from vars), select b from vars);<br \/>\n<strong>merlinm<\/strong>\toops, missing a parent there<br \/>\n<strong>merlinm<\/strong>\tgah<br \/>\n<strong>RhodiumToad<\/strong>\twith vars(a,b) as (values (&#8216;a&#8217;, 2)) &#8230;<br \/>\n<strong>merlinm<\/strong>\tah, touche<br \/>\n<strong>darkblue_b<\/strong>\twith .. is a &#8216;materialized subselect&#8217; under the hood ?<br \/>\n<strong>RhodiumToad<\/strong>\tyes<br \/>\n<strong>strk<\/strong>\tnot really materialized, but yes<br \/>\n<strong>RhodiumToad<\/strong>\tit is materialized<br \/>\n<strong>strk<\/strong>\tdoesn&#8217;t persist,is what I mean<br \/>\n<strong>strk<\/strong>\tnot after the statement<br \/>\n<strong>darkblue_b<\/strong>\tah &#8211; its a temporary table&#8230; perhaps<br \/>\n<strong>RhodiumToad<\/strong>\tno<br \/>\n<strong>darkblue_b<\/strong>\tok &#8211; well I dont want to waste your time.. just grasping at things.<br \/>\n<strong>strk<\/strong>\t<em>darkblue_b<\/em>: only exists during the single statement<br \/>\n<strong>darkblue_b<\/strong>\tyes I read that<br \/>\n<strong>RhodiumToad<\/strong>\tit exists only within the scope of the query and it can&#8217;t have indexes, but it is materialized in that it is evaluated at most once (lazily)<br \/>\n<strong>darkblue_b<\/strong>\t<em>writes that down<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>RhodiumToad with foo(x) as (values (&#8216;blah blah blah&#8217;)) insert into wherever select x from foo; merlinm or, with vars as (select &#8216;a&#8217; as a, 2 as b) insert into foo values (select a from vars), select b from vars); merlinm oops, missing a parent there merlinm gah RhodiumToad with vars(a,b) as (values (&#8216;a&#8217;, 2)) &#8230; [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"_links":{"self":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1413"}],"collection":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1413"}],"version-history":[{"count":2,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1413\/revisions"}],"predecessor-version":[{"id":1421,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1413\/revisions\/1421"}],"wp:attachment":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1413"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1413"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1413"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}