CODEWITHSUNDEEP
sqlpostgresql
Wolf_Tru
Wolf_Tru

Reputation: 563

parentheses after an alias name

I came across a neat article about using generate_series to create table data. the author does a great job explaining the code, there is however some things I still don't understand. the x after the alias comp3syl(x) and z2(x) is that assigning the derived table's column name to x? Also what is this syntax called? I'm having trouble googling it's documentation without some keywords

SELECT(
SELECT concat_ws(' ',name_first, name_last) as generated
FROM (
    SELECT string_agg(x,'')
    FROM (
        select start_arr[ 1 + ( (random() * 25)::int) % 16 ]
        FROM
        (
            select '{CO,GE,FOR,SO,CO,GIM,SE,CO,GE,CA,FRA,GEC,GE,GA,FRO,GIP}'::text[] as start_arr
        ) syllarr,
        -- need 3 syllabes, and force generator interpretation with the '*0' (else 3 same syllabes)
        generate_series(1, 3 + (generator*0))
    ) AS comp3syl(x)
) AS comp_name_1st(name_first),
(
    SELECT x[ 1 + ( (random() * 25)::int) % 14 ]
    FROM (
        select '{Ltd,& Co,SARL,SA,Gmbh,United,Brothers,& Sons,International,Ext,Worldwide,Global,2000,3000}'::text[]
    ) AS z2(x)
) AS comp_name_last(name_last)
)
FROM generate_series(1,10000) as generator

for anyone interested here is the link to the article: https://regilero.github.io/postgresql/english/2017/06/26/postgresql_advanced_generate_series/

Upvotes: 6

Views: 4436

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270411

In some databases, but not all, a table alias can take a list of column aliases. This is how the columns defined by the table alias are referred to.

Consider the following two queries:

select *
from (values (1)) x;

select *
from (values (1)) x(a);

In the first, the one column returned is unnamed. In the second, the column is called "a".

The reason for using it with generate_series() is that the function returns a column -- a really useful column, in fact. But how do you refer to it? I usually use something like gs(n) so the column is referred to as gs.n. You can also use just generate_series() n and the column can be referred to as either n or n.n.

For the subquery itself, I always put the column alias inside the subquery. Not all databases support the naming of columns with table aliases, so it is easier to name the columns in the subquery.

Upvotes: 4

Gregory Arenius
Gregory Arenius

Reputation: 3204

From the documentation:

Another form of table aliasing gives temporary names to the columns of the table, as well as the table itself:

FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )

If fewer column aliases are specified than the actual table has columns, the remaining columns are not renamed. This syntax is especially useful for self-joins or subqueries.

In the code you gave the generate_series() calls aren't aliased in the subqueries themselves so the author names them when he aliases the subquery.

Upvotes: 7

Related Questions