This section describes functions that possibly return more than one row. The most widely used functions in this class are series generating functions, as detailed in <xref linkend="functions-srf-series"/> and <xref linkend="functions-srf-subscripts"/>. Other, more specialized set-returning functions are described elsewhere in this manual. See <xref linkend="queries-tablefunctions"/> for ways to combine multiple set-returning functions. 本節では、場合により複数行を返す関数について説明します。 このクラスで最も広く用いられている関数は、表 9.65、および表 9.66にて詳細が触れられている、連続値生成関数です。 他方、より特化された集合を返す関数の記述がこのマニュアルの他の場所にあります。 集合を返す関数を複数組み合わせる方法については7.2.1.4を参照してください。
表9.65 連続値生成関数
Function 関数 Description 説明 |
|---|
Generates a series of values from <parameter>start</parameter>
to <parameter>stop</parameter>, with a step size
of <parameter>step</parameter>. <parameter>step</parameter>
defaults to 1.
|
Generates a series of values from <parameter>start</parameter>
to <parameter>stop</parameter>, with a step size
of <parameter>step</parameter>.
In the timezone-aware form, times of day and daylight-savings
adjustments are computed according to the time zone named by
the <parameter>timezone</parameter> argument, or the current
<xref linkend="guc-timezone"/> setting if that is omitted.
|
When <parameter>step</parameter> is positive, zero rows are returned if
<parameter>start</parameter> is greater than <parameter>stop</parameter>.
Conversely, when <parameter>step</parameter> is negative, zero rows are
returned if <parameter>start</parameter> is less than <parameter>stop</parameter>.
Zero rows are also returned if any input is <literal>NULL</literal>.
It is an error
for <parameter>step</parameter> to be zero. Some examples follow:
stepが正の場合、startがstopよりも大きいと０行が返ります。
反対に、stepが負の場合は、startがstopよりも小さいと０行が返ります。
また、どれかの入力がNULLの場合も０行が返ります。
stepが０の時はエラーになります。
以下にいくつか例を示します。
SELECT * FROM generate_series(2,4);
generate_series
-----------------
2
3
4
(3 rows)
SELECT * FROM generate_series(5,1,-2);
generate_series
-----------------
5
3
1
(3 rows)
SELECT * FROM generate_series(4,3);
generate_series
-----------------
(0 rows)
SELECT generate_series(1.1, 4, 1.3);
generate_series
-----------------
1.1
2.4
3.7
(3 rows)
-- this example relies on the date-plus-integer operator:
-- この例は日付に整数を足し込む演算子に依存します。
SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
dates
------------
2004-02-05
2004-02-12
2004-02-19
(3 rows)
SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
'2008-03-04 12:00', '10 hours');
generate_series
---------------------
2008-03-01 00:00:00
2008-03-01 10:00:00
2008-03-01 20:00:00
2008-03-02 06:00:00
2008-03-02 16:00:00
2008-03-03 02:00:00
2008-03-03 12:00:00
2008-03-03 22:00:00
2008-03-04 08:00:00
(9 rows)
-- this example assumes that TimeZone is set to UTC; note the DST transition:
SELECT * FROM generate_series('2001-10-22 00:00 -04:00'::timestamptz,
'2001-11-01 00:00 -05:00'::timestamptz,
'1 day'::interval, 'America/New_York');
generate_series
------------------------
2001-10-22 04:00:00+00
2001-10-23 04:00:00+00
2001-10-24 04:00:00+00
2001-10-25 04:00:00+00
2001-10-26 04:00:00+00
2001-10-27 04:00:00+00
2001-10-28 04:00:00+00
2001-10-29 05:00:00+00
2001-10-30 05:00:00+00
2001-10-31 05:00:00+00
2001-11-01 05:00:00+00
(11 rows)
表9.66 添え字生成関数
<function>generate_subscripts</function> is a convenience function that generates
the set of valid subscripts for the specified dimension of the given
array.
Zero rows are returned for arrays that do not have the requested dimension,
or if any input is <literal>NULL</literal>.
Some examples follow:
generate_subscriptsは、指定した配列の指定した次数で有効な添え字からなる集合を生成するために便利な関数です。
要求された次数を持たない配列またはどれかの入力がNULLなら0行が返ります。
いくつかの例を以下に示します。
-- basic usage:
SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
s
---
1
2
3
4
(4 rows)
-- presenting an array, the subscript and the subscripted
-- value requires a subquery:
-- 配列、添え字とその添え字が示す値を表示するには
-- 副問い合わせが必要です。
SELECT * FROM arrays;
a
--------------------
{-1,-2}
{100,200,300}
(2 rows)
SELECT a AS array, s AS subscript, a[s] AS value
FROM (SELECT generate_subscripts(a, 1) AS s, a FROM arrays) foo;
array | subscript | value
---------------+-----------+-------
{-1,-2} | 1 | -1
{-1,-2} | 2 | -2
{100,200,300} | 1 | 100
{100,200,300} | 2 | 200
{100,200,300} | 3 | 300
(5 rows)
-- unnest a 2D array:
-- 2次元配列の入れ子を解きます。
CREATE OR REPLACE FUNCTION unnest2(anyarray)
RETURNS SETOF anyelement AS $$
select $1[i][j]
from generate_subscripts($1,1) g1(i),
generate_subscripts($1,2) g2(j);
$$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION
SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
unnest2
---------
1
2
3
4
(4 rows)
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 function's output column(s), 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 <function>unnest()</function>.
FROM句の関数の後にWITH ORDINALITYが付いている場合、1から始まり関数の出力の行毎に1増えていくbigint列が関数の出力列に追加されます。
これはunnest()のような集合を返す関数の場合に最も役に立ちます。
-- set returning function WITH ORDINALITY:
-- 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_xact | 12
pg_snapshots | 13
pg_multixact | 14
PG_VERSION | 15
pg_wal | 16
pg_hba.conf | 17
pg_stat_tmp | 18
pg_subtrans | 19
(19 rows)