Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5c292e6

Browse files
committed
Declare lead() and lag() using anycompatible not anyelement.
This allows use of a "default" expression that doesn't slavishlymatch the data column's type. Formerly you got something like"function lag(numeric, integer, integer) does not exist", whichis not just unhelpful but actively misleading.The SQL spec suggests that the default should be coerced to the datacolumn's type, but this implementation instead chooses the commonsupertype, which seems at least as reasonable.(Note: I took the opportunity to run "make reformat-dat-files" onpg_proc.dat, so this commit includes some cosmetic changes torecently-added entries that aren't related to lead/lag.)Vik FearingDiscussion:https://postgr.es/m/77675130-89da-dab1-51dd-492c93dcf5d1@postgresfriends.org
1 parent40c24bf commit5c292e6

File tree

5 files changed

+58
-24
lines changed

5 files changed

+58
-24
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -19594,17 +19594,17 @@ SELECT count(*) FROM sometable;
1959419594
<indexterm>
1959519595
<primary>lag</primary>
1959619596
</indexterm>
19597-
<function>lag</function> ( <parameter>value</parameter> <type>anyelement</type>
19597+
<function>lag</function> ( <parameter>value</parameter> <type>anycompatible</type>
1959819598
<optional>, <parameter>offset</parameter> <type>integer</type>
19599-
<optional>, <parameter>default</parameter> <type>anyelement</type> </optional></optional> )
19600-
<returnvalue>anyelement</returnvalue>
19599+
<optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> )
19600+
<returnvalue>anycompatible</returnvalue>
1960119601
</para>
1960219602
<para>
1960319603
Returns <parameter>value</parameter> evaluated at
1960419604
the row that is <parameter>offset</parameter>
1960519605
rows before the current row within the partition; if there is no such
1960619606
row, instead returns <parameter>default</parameter>
19607-
(which must be ofthe sametypeas
19607+
(which must be ofatypecompatible with
1960819608
<parameter>value</parameter>).
1960919609
Both <parameter>offset</parameter> and
1961019610
<parameter>default</parameter> are evaluated
@@ -19619,17 +19619,17 @@ SELECT count(*) FROM sometable;
1961919619
<indexterm>
1962019620
<primary>lead</primary>
1962119621
</indexterm>
19622-
<function>lead</function> ( <parameter>value</parameter> <type>anyelement</type>
19622+
<function>lead</function> ( <parameter>value</parameter> <type>anycompatible</type>
1962319623
<optional>, <parameter>offset</parameter> <type>integer</type>
19624-
<optional>, <parameter>default</parameter> <type>anyelement</type> </optional></optional> )
19625-
<returnvalue>anyelement</returnvalue>
19624+
<optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> )
19625+
<returnvalue>anycompatible</returnvalue>
1962619626
</para>
1962719627
<para>
1962819628
Returns <parameter>value</parameter> evaluated at
1962919629
the row that is <parameter>offset</parameter>
1963019630
rows after the current row within the partition; if there is no such
1963119631
row, instead returns <parameter>default</parameter>
19632-
(which must be ofthe sametypeas
19632+
(which must be ofatypecompatible with
1963319633
<parameter>value</parameter>).
1963419634
Both <parameter>offset</parameter> and
1963519635
<parameter>default</parameter> are evaluated

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO202011042
56+
#defineCATALOG_VERSION_NO202011043
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 17 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1563,11 +1563,11 @@
15631563
proname => 'string_to_array', proisstrict => 'f', prorettype => '_text',
15641564
proargtypes => 'text text text', prosrc => 'text_to_array_null' },
15651565
{ oid => '8432', descr => 'split delimited text',
1566-
proname => 'string_to_table',proisstrict => 'f',prorows => '1000',
1566+
proname => 'string_to_table',prorows => '1000',proisstrict => 'f',
15671567
proretset => 't', prorettype => 'text', proargtypes => 'text text',
15681568
prosrc => 'text_to_table' },
15691569
{ oid => '8433', descr => 'split delimited text, with null string',
1570-
proname => 'string_to_table',proisstrict => 'f',prorows => '1000',
1570+
proname => 'string_to_table',prorows => '1000',proisstrict => 'f',
15711571
proretset => 't', prorettype => 'text', proargtypes => 'text text text',
15721572
prosrc => 'text_to_table_null' },
15731573
{ oid => '395',
@@ -5257,8 +5257,8 @@
52575257
proargnames => '{pid,status,receive_start_lsn,receive_start_tli,written_lsn,flushed_lsn,received_tli,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time,slot_name,sender_host,sender_port,conninfo}',
52585258
prosrc => 'pg_stat_get_wal_receiver' },
52595259
{ oid => '8595', descr => 'statistics: information about replication slots',
5260-
proname => 'pg_stat_get_replication_slots', prorows => '10', proisstrict => 'f',
5261-
proretset => 't', provolatile => 's', proparallel => 'r',
5260+
proname => 'pg_stat_get_replication_slots', prorows => '10',
5261+
proisstrict => 'f',proretset => 't', provolatile => 's', proparallel => 'r',
52625262
prorettype => 'record', proargtypes => '',
52635263
proallargtypes => '{text,int8,int8,int8,int8,int8,int8,timestamptz}',
52645264
proargmodes => '{o,o,o,o,o,o,o,o}',
@@ -5491,8 +5491,7 @@
54915491
{ oid => '1136', descr => 'statistics: information about WAL activity',
54925492
proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's',
54935493
proparallel => 'r', prorettype => 'record', proargtypes => '',
5494-
proallargtypes => '{int8,timestamptz}',
5495-
proargmodes => '{o,o}',
5494+
proallargtypes => '{int8,timestamptz}', proargmodes => '{o,o}',
54965495
proargnames => '{wal_buffers_full,stats_reset}',
54975496
prosrc => 'pg_stat_get_wal' },
54985497

@@ -5615,8 +5614,9 @@
56155614
prorettype => 'void', proargtypes => 'text', prosrc => 'pg_stat_reset_slru' },
56165615
{ oid => '8596',
56175616
descr => 'statistics: reset collected statistics for a single replication slot',
5618-
proname => 'pg_stat_reset_replication_slot', proisstrict => 'f', provolatile => 'v',
5619-
prorettype => 'void', proargtypes => 'text', prosrc => 'pg_stat_reset_replication_slot' },
5617+
proname => 'pg_stat_reset_replication_slot', proisstrict => 'f',
5618+
provolatile => 'v', prorettype => 'void', proargtypes => 'text',
5619+
prosrc => 'pg_stat_reset_replication_slot' },
56205620

56215621
{ oid => '3163', descr => 'current trigger depth',
56225622
proname => 'pg_trigger_depth', provolatile => 's', proparallel => 'r',
@@ -7833,9 +7833,11 @@
78337833
prosrc => 'pg_get_shmem_allocations' },
78347834

78357835
# memory context of local backend
7836-
{ oid => '2282', descr => 'information about all memory contexts of local backend',
7837-
proname => 'pg_get_backend_memory_contexts', prorows => '100', proretset => 't',
7838-
provolatile => 'v', proparallel => 'r', prorettype => 'record', proargtypes => '',
7836+
{ oid => '2282',
7837+
descr => 'information about all memory contexts of local backend',
7838+
proname => 'pg_get_backend_memory_contexts', prorows => '100',
7839+
proretset => 't', provolatile => 'v', proparallel => 'r',
7840+
prorettype => 'record', proargtypes => '',
78397841
proallargtypes => '{text,text,text,int4,int8,int8,int8,int8,int8}',
78407842
proargmodes => '{o,o,o,o,o,o,o,o,o}',
78417843
proargnames => '{name, ident, parent, level, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes}',
@@ -9748,8 +9750,8 @@
97489750
proname => 'lag', prokind => 'w', prorettype => 'anyelement',
97499751
proargtypes => 'anyelement int4', prosrc => 'window_lag_with_offset' },
97509752
{ oid => '3108', descr => 'fetch the Nth preceding row value with default',
9751-
proname => 'lag', prokind => 'w', prorettype => 'anyelement',
9752-
proargtypes => 'anyelement int4anyelement',
9753+
proname => 'lag', prokind => 'w', prorettype => 'anycompatible',
9754+
proargtypes => 'anycompatible int4anycompatible',
97539755
prosrc => 'window_lag_with_offset_and_default' },
97549756
{ oid => '3109', descr => 'fetch the following row value',
97559757
proname => 'lead', prokind => 'w', prorettype => 'anyelement',
@@ -9758,8 +9760,8 @@
97589760
proname => 'lead', prokind => 'w', prorettype => 'anyelement',
97599761
proargtypes => 'anyelement int4', prosrc => 'window_lead_with_offset' },
97609762
{ oid => '3111', descr => 'fetch the Nth following row value with default',
9761-
proname => 'lead', prokind => 'w', prorettype => 'anyelement',
9762-
proargtypes => 'anyelement int4anyelement',
9763+
proname => 'lead', prokind => 'w', prorettype => 'anycompatible',
9764+
proargtypes => 'anycompatible int4anycompatible',
97639765
prosrc => 'window_lead_with_offset_and_default' },
97649766
{ oid => '3112', descr => 'fetch the first row value',
97659767
proname => 'first_value', prokind => 'w', prorettype => 'anyelement',

‎src/test/regress/expected/window.out

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -300,6 +300,21 @@ SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM t
300300
0 | 3 | 3
301301
(10 rows)
302302

303+
SELECT lag(ten, four, 0.7) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten;
304+
lag | ten | four
305+
-----+-----+------
306+
0 | 0 | 0
307+
0 | 0 | 0
308+
4 | 4 | 0
309+
0.7 | 1 | 1
310+
1 | 1 | 1
311+
1 | 7 | 1
312+
7 | 9 | 1
313+
0.7 | 0 | 2
314+
0.7 | 1 | 3
315+
0.7 | 3 | 3
316+
(10 rows)
317+
303318
SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
304319
lead | ten | four
305320
------+-----+------
@@ -345,6 +360,21 @@ SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FRO
345360
-1 | 3 | 3
346361
(10 rows)
347362

363+
SELECT lead(ten * 2, 1, -1.4) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten;
364+
lead | ten | four
365+
------+-----+------
366+
0 | 0 | 0
367+
8 | 0 | 0
368+
-1.4 | 4 | 0
369+
2 | 1 | 1
370+
14 | 1 | 1
371+
18 | 7 | 1
372+
-1.4 | 9 | 1
373+
-1.4 | 0 | 2
374+
6 | 1 | 3
375+
-1.4 | 3 | 3
376+
(10 rows)
377+
348378
SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
349379
first_value | ten | four
350380
-------------+-----+------

‎src/test/regress/sql/window.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -63,12 +63,14 @@ SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHER
6363
SELECT lag(ten, four) OVER (PARTITION BY fourORDER BY ten), ten, fourFROM tenk1WHERE unique2<10;
6464

6565
SELECT lag(ten, four,0) OVER (PARTITION BY fourORDER BY ten), ten, fourFROM tenk1WHERE unique2<10;
66+
SELECT lag(ten, four,0.7) OVER (PARTITION BY fourORDER BY ten), ten, fourFROM tenk1WHERE unique2<10ORDER BY four, ten;
6667

6768
SELECT lead(ten) OVER (PARTITION BY fourORDER BY ten), ten, fourFROM tenk1WHERE unique2<10;
6869

6970
SELECT lead(ten*2,1) OVER (PARTITION BY fourORDER BY ten), ten, fourFROM tenk1WHERE unique2<10;
7071

7172
SELECT lead(ten*2,1,-1) OVER (PARTITION BY fourORDER BY ten), ten, fourFROM tenk1WHERE unique2<10;
73+
SELECT lead(ten*2,1,-1.4) OVER (PARTITION BY fourORDER BY ten), ten, fourFROM tenk1WHERE unique2<10ORDER BY four, ten;
7274

7375
SELECT first_value(ten) OVER (PARTITION BY fourORDER BY ten), ten, fourFROM tenk1WHERE unique2<10;
7476

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp