postgresql 9.4引入的with ordinaly在lightdb中使用rownum替换

pg 9.4为表函数引入了自动生成行号的功能,如下:

=# SELECT * FROM generate_series(4,1,-1) WITH ORDINALITY;
 generate_series | ordinality 
-----------------+------------
               4 |          1
               3 |          2
               2 |          3
               1 |          4

但是它不能用于普通表。在lightdb中,可以使用rownum对应,同时应用于表函数和表。如下:

zjh@postgres=# select rownum,rowid,id,v from big_table limit 10 offset 10;
 rownum | rowid  | id  |              v               
--------+--------+-----+------------------------------
     11 | (0,11) | 354 | fwisifwieisvfwismvmfeiwis354
     12 | (0,12) | 398 | fwisifwieisvfwismvmfeiwis398
     13 | (0,13) | 443 | fwisifwieisvfwismvmfeiwis443
     14 | (0,14) | 588 | fwisifwieisvfwismvmfeiwis588
     15 | (0,15) | 611 | fwisifwieisvfwismvmfeiwis611
     16 | (0,16) | 686 | fwisifwieisvfwismvmfeiwis686
     17 | (0,17) | 742 | fwisifwieisvfwismvmfeiwis742
     18 | (0,18) | 762 | fwisifwieisvfwismvmfeiwis762
     19 | (0,19) | 791 | fwisifwieisvfwismvmfeiwis791
     20 | (0,20) | 850 | fwisifwieisvfwismvmfeiwis850
(10 rows)


zjh@postgres=# SELECT rownum,x FROM generate_series(4,1,-1) x;
 rownum | x 
--------+---
      1 | 4
      2 | 3
      3 | 2
      4 | 1
(4 rows)

具有更好的通用性。

https://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-with-ordinality/

http://www.light-pg.com/docs/lightdb/13.8-22.3/functions-srf.html

posted @ 2022-12-13 20:44  zhjh256  阅读(73)  评论(0编辑  收藏  举报