What does this "t" do?

Question

I'm having trouble understanding the following example from the PostgreSQL documentation:

-- set returning function WITH ORDINALITY
SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
       ls        | n
-----------------+----
 pg_serial       |  1
 pg_twophase     |  2
 postmaster.opts |  3
 pg_notify       |  4
...

The things inside the parentheses of the t(...) become the column names, but what is the t itself? I'm asking here because the docs don't explain it, and a single-letter function is ungoogleable. In fact, the docs don't even explain what is supposed to come after AS; the only thing we get is this one example.

It seems I can replace t by any other identifier, and it still works.

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

4

The syntax you're looking for is:

function_call [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]
https://www.postgresql.org/docs/10/static/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS

t is an arbitrary table alias; you can give it any valid name you want.

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 01 '17 at 09:32

deceze

510,633
85
743
889

Thanks! Guess I wasn't looking at the canonical part of the docs after all. – Thomas Dec 01 '17 at 09:39

score 1 · Answer 2 · answered Dec 01 '17 at 09:31

it's alias for a set, to be able to reference it in column list, eg:

SELECT t.*,pg_database .datname 
FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n) 
join pg_database on true where datname = 'postgres'
          ls          | n  | datname
----------------------+----+----------
 pg_dynshmem          |  1 | postgres
 postmaster.pid       |  2 | postgres
 PG_VERSION           |  3 | postgres
 base                 |  4 | postgres

What does this "t" do?

2 Answers2