generate_subscripts($1,2) g2(j);

$$ LANGUAGE sql IMMUTABLE;

CREATE FUNCTION

postgres=#SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);

SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);

unnest2

---------

1

2

3

4

(4 rows)

</programlisting>

</para>

<indexterm>

<primary>ordinality</primary>

</indexterm>

<para>

When a function in the <literal>FROM</literal> clause is suffixed by

<literal>WITH ORDINALITY</literal>, a <type>bigint</type> column is appended

to the output which starts from 1 and increments by 1 for each row of the

function's output. This is most useful in the case of set returning functions

such as UNNEST(). This functionality is available for functions returning

composite types or using <literal>OUT</literal> parameters, but not when using

a function returning <literal>RECORD</literal> with an explicit column

definition list.

<programlisting>

-- 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

postgresql.conf | 5

pg_tblspc | 6

logfile | 7

base | 8

postmaster.pid | 9

pg_ident.conf | 10

global | 11

pg_clog | 12

pg_snapshots | 13

pg_multixact | 14

PG_VERSION | 15

pg_xlog | 16

pg_hba.conf | 17

pg_stat_tmp | 18

pg_subtrans | 19

(19 rows)

</programlisting>

</para>

[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]

[ LATERAL ] ( <replaceable class="parameter">select</replaceable> ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ]

<replaceable class="parameter">with_query_name</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]

[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] | <replaceable class="parameter">column_definition</replaceable> [, ...] ) ]

[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]

[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ] <replaceable class="parameter">alias</replaceable> ( <replaceable class="parameter">column_definition</replaceable> [, ...] )

[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )

<replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) ]

clause. (This is especially useful for functions that return

result sets, but any function can be used.) This acts as

though its output were created as a temporary table for the

duration of this single <command>SELECT</command> command. An

alias can also be used. If an alias is written, a column alias

list can also be written to provide substitute names for one

or more attributes of the function's composite return type. If

the function has been defined as returning the <type>record</>

data type, then an alias or the key word <literal>AS</> must

be present, followed by a column definition list in the form

<literal>( <replaceable

duration of this single <command>SELECT</command> command.

When the optional <command>WITH ORDINALITY</command> is

appended to the function call, a new column is appended after

all the function call's columns with numbering for each row.

For example:

<programlisting>

SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;

unnest | ordinality

--------+----------

a | 1

b | 2

c | 3

d | 4

e | 5

f | 6

(6 rows)

</programlisting>

An alias can also be used. If an alias is written, a column

alias list can also be written to provide substitute names for

one or more attributes of the function's composite return

type, including the column added by <literal>ORDINALITY</literal>

if present.

</para>

<para>

If the function has been defined as returning the

<type>record</> data type, then an alias or the key word

<literal>AS</> must be present, followed by a column

definition list in the form <literal>( <replaceable

class="parameter">column_name</replaceable> <replaceable

class="parameter">data_type</replaceable> <optional>, ... </>

)</literal>. The column definition list must match the actual

number and types of columns returned by the function.

class="parameter">data_type</replaceable> <optional>, ...

</>)</literal>. The column definition list must match the

actual number and types of columns returned by the function.

<literal>ORDINALITY</literal> does not work in this case.

</para>

</listitem>

</varlistentry>

returndesc;

}

CreateTupleDescCopyExtend(TupleDesctupdesc,intmoreatts)

{

TupleDescdesc;

inti;

intsrc_natts=tupdesc->natts;

Assert(moreatts >=0);

desc=CreateTemplateTupleDesc(src_natts+moreatts,tupdesc->tdhasoid);

for (i=0;i<src_natts;i++)

{

memcpy(desc->attrs[i],tupdesc->attrs[i],ATTRIBUTE_FIXED_PART_SIZE);

desc->attrs[i]->attnotnull= false;

desc->attrs[i]->atthasdef= false;

}

returndesc;

}