# LATERAL Published articles for LATERAL. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## LATERAL is Your Friend to Create Local Column Variables in SQL DevFeed: [LATERAL is Your Friend to Create Local Column Variables in SQL]() Original publisher: [Read original article]() Author: lukaseder Published: 2022-11-04T08:56:17Z Content type: tutorial Language: en Sources: [jOOQ]() Topics: [SQL](), [Databases]() Tags: [apply](), [cross-apply](), [cross-join-lateral](), [derived-table](), [examples](), [lateral](), [lateral-derived-table](), [local-variables](), [oracle](), [outer-apply](), [postgresql](), [sql](), [sql-server](), [variables]() ### AI overview This article explains how SQL's LATERAL clause can be used to define local column variables next to the tables that provide their values. It compares this approach with derived tables and notes database-specific syntax, including SQL Server's CROSS APPLY. ### Source excerpt The standard SQL WITH clause has been tremendously helpful in structuring SQL queries. Instead of nesting everything in unreadable derived tables like this: People have started moving the logic up front, just like in any other programming language, where we declare things first, lexically, then use them: Both queries will produce the 5 actors with ... Continue reading LATERAL is Your Friend to Create Local Column Variables in SQL -> ## Postgres hidden gems DevFeed: [Postgres hidden gems]() Original publisher: [Read original article]() Author: Map Published: 2018-01-31T20:55:56Z Content type: article Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [Database](), [Replication](), [Network Operations](), [Statistics]() Tags: [functions](), [jsonb](), [lateral](), [network-operations](), [postgres](), [postgresql](), [psql](), [replication](), [statistics](), [text](), [unicode](), [window-functions]() ### AI overview The article collects PostgreSQL features that the author and contributors consider useful or easily overlooked. It highlights capabilities including ltree, citext, date arithmetic, jsonb, lateral joins, window functions, custom foreign data wrappers, replication slots, range types, statistics, logical decoding, and psql features. ### Source excerpt Postgres has a rich set of features, even when working everyday with it you may not discover all it has to offer. In hopes of learning some new features that I didn't know about myself as well as seeing what small gems people found joy in I tweeted out to see what people came back from. The response was impressive, and rather than have it lost into ether of twitter I'm capturing some of the responses here along with some resources many of the features. @listrophy - $ brew postgresql-update database Though personally I prefer Postgres.app ;) @pat_shaugnessy - ltree Pat has a great post that walks through ltree @billyfung - citext A really handy datatype for case insensitive text @eeeebbbbrrrr - date math with intervals I couldn't agree more on this one, working with time in Postgres is the easiest time I've every had @DataMiller - The jsonb datatype and lateral joins I'd argue it's hard to claim now JSONB is a hidden gem, but lateral joins are certain a great one @ideasasylum - row_number() over(partition http://orders.site_id order by orders.created_at) Window functions are definitely a handy feature was my hidden (to me) discovery this week @franckverrot - Index access method, and custom FDWs @jonjensen0 - Set-returning functions and custom aggregate functions can be very helpful. @ascherbaum - psql -x Psql is indeed awesome and can be well tuned @Abstr_ct - The fact that the docs are fantastic and all hidden gems are actually readily available. Oh, and pl/brainfuck obviously @Halpin_IO - Subnetting and network operations @jkatz05 - Replication slots, both physical and logical. They've made setting up replication infinitely easier. And range types. Because they're awesome. @petereisentraut - Unicode table borders @_avichalp Notify/listen @simonw - The fact that GIN indices can make LIKE queries run fast even if the % isn't just at the end of the string @javisantana - it has a statistics system to plan queries that can be used by the user when accuracy does not matt ## Set Returning Functions and PostgreSQL 10 DevFeed: [Set Returning Functions and PostgreSQL 10]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2017-10-13T11:25:21Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [function](), [SQL](), [JSON](), [Query (disambiguation)]() Tags: [functions](), [json](), [jsonb](), [lateral](), [postgresql](), [sql]() ### AI overview This article explains how PostgreSQL 10 changed the evaluation and permitted contexts of set-returning functions. It reviews clearer semantics for functions in SELECT lists, including handling of multiple functions with different row counts, and explains restrictions in CASE and COALESCE expressions. ### Source excerpt PostgreSQL 10 is now available for everyone to use, and hinted by David Fetter I had to review my previous article on Json and SQL to adapt to Set Returning Functions changes. A Set Returning Function is a PostgreSQL Stored Procedure that can be used as a relation: from a single call it returns an entire result set, much like a subquery or a table. It used to be possible to use SRF in the SELECT clause, with dubious (but useful at times) semantics, and also in scalar contexts. The semantics have been fixed and are now much clearer, and the uses in scalar contexts are forbidden -- they were a hack and never made sense anyway.