Shulou(Shulou.com)06/02 Report--

PostgreSQL which window functions, many novices are not very clear, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Window function:

The window function performs calculations in a set of table rows that are related to the current row in some way. This is equivalent to the type of calculation that can be done using aggregate functions. However, window functions do not cause rows to be grouped into individual output lines, just like non-window aggregation calls. Instead, the row retains its independent identity. Behind the scenes, the window function can not only access the current row of the query result.

Examples of row_number usage:

[postgres@shawnpc bin] $. / psql psql (13devel) Type "help" for help.postgres=# select row_number () over () as rownum, id from aa; rownum | id-+-1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 6 | 6 7 | 7 8 | 8 9 | 9 10 | 10 (10 rows) postgres=#

Row_number Code:

/ * row_number * just increment up from 1 until current partition finishes. * / Datumwindow_row_number (PG_FUNCTION_ARGS) {WindowObject winobj = PG_WINDOW_OBJECT (); / / get the window function memory context int64 curpos = WinGetCurrentPosition (winobj); / / initialize the location WinSetMarkPosition (winobj, curpos); / / bind the line number to the location PG_RETURN_INT64 (curpos + 1); / / return the line number}

It may seem very simple, but after debugging, it is found that this is highly coupled with the execution plan: set the function breakpoint:

Breakpoint 1, window_row_number (fcinfo=0x7ffc158cce90) at windowfuncs.c:8383 {(gdb) bt#0 window_row_number (fcinfo=0x7ffc158cce90) at windowfuncs.c:83#1 0x0000000000632956 in eval_windowfunction (perfuncstate=0x1ca3768, result=0x1ca3738, isnull=0x1ca3750, winstate=0x1ca23e8, winstate=0x1ca23e8) at nodeWindowAgg.c:1056#2 0x0000000000635174 in ExecWindowAgg (pstate=0x1ca23e8) at nodeWindowAgg.c:2198#3 0x0000000000605b82 in ExecProcNode (node=0x1ca23e8) at.. / src/include/executor/executor.h:240#4 ExecutePlan (execute_once=, dest=0x1c125e8, direction=, numberTuples=0, sendTuples=true, operation=CMD_SELECT Use_parallel_mode=, planstate=0x1ca23e8, estate=0x1ca21c0) at execMain.c:1648#5 standard_ExecutorRun (queryDesc=0x1c0eb70, direction=, count=0, execute_once=) at execMain.c:365#6 0x000000000074c81b in PortalRunSelect (portal=portal@entry=0x1c52e90, forward=forward@entry=true, count=0, count@entry=9223372036854775807, dest=dest@entry=0x1c125e8) at pquery.c:929#7 0x000000000074db60 in PortalRun (portal=portal@entry=0x1c52e90, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0x1c125e8, altdest=altdest@entry=0x1c125e8 CompletionTag=completionTag@entry=0x7ffc158cd7e0 "") at pquery.c:770#8 0x0000000000749bc6 in exec_simple_query (query_string=0x1becfa0 "select row_number () over () as rownum, id from aa ") at postgres.c:1231#9 0x000000000074aea2 in PostgresMain (argc=, argv=argv@entry=0x1c16f70, dbname=0x1c16e98" postgres ", username=) at postgres.c:4256#10 0x000000000047e579 in BackendRun (port=, port=) at postmaster.c:4446#11 BackendStartup (port=0x1c0ee70) at postmaster.c:4137#12 ServerLoop () at postmaster.c:1704#13 0x00000000006ddb9d in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0x1be7bb0) at postmaster.c:1377#14 0x000000000047f243 in main (argc=3, argv=0x1be7bb0) at main.c:210

As can be seen from above, the first row_number function execution is called after the execution plan is executed. The first thing to enter is ExecutePlan:

Static voidExecutePlan (EState * estate, PlanState * planstate, bool use_parallel_mode, CmdType operation, bool sendTuples, uint64 numberTuples, ScanDirection direction, DestReceiver * dest Bool execute_once) {TupleTableSlot * slot Uint64 current_tuple_count; slightly for (;;) {/ * Reset the per-output-tuple exprcontext * / ResetPerTupleExprContext (estate); / * Execute the plan and obtain a tuple * / slot = ExecProcNode (planstate); slightly}

Here ExecProcNode (macro definition, called ExecWindowAgg) is called, ExecWindowAgg calls eval_windowfunction, and it is eval_windowfunction that completes the call to row_number and builds the relevant data. By debugging, you can see that row_number will be called as many times as many rows of data.

Eval_windowfunction:

/ * eval_windowfunction * * Arguments of window functions are not evaluated here, because a window * function can need random access to arbitrary rows in the partition. * The window function uses the special WinGetFuncArgInPartition and * WinGetFuncArgInFrame functions to evaluate the arguments for the rows * it wants. * / static voideval_windowfunction (WindowAggState * winstate, WindowStatePerFunc perfuncstate, Datum * result, bool * isnull) {LOCAL_FCINFO (fcinfo, FUNC_MAX_ARGS); MemoryContext oldContext; oldContext = MemoryContextSwitchTo (winstate- > ss.ps.ps_ExprContext- > ecxt_per_tuple_memory) / / switch to the memory context of tuple / * * We don't pass any normal arguments to a window function, but we do pass * it the number of arguments, in order to permit window function * implementations to support varying numbers of arguments. The real info * goes through the WindowObject, which is passed via fcinfo- > context. * / InitFunctionCallInfoData (* fcinfo, & (perfuncstate- > flinfo), perfuncstate- > numArguments, perfuncstate- > winCollation, (void *) perfuncstate- > winobj, NULL) / / initialize fcinfo, using / * Just in case, make all the regular argument slots be null * / for (int argno = 0; argno) for the following calling functions

NumArguments; argno++) fcinfo- > args [argno] .isnull = true;// see note / * Window functions don't have a current aggregate context, either * / winstate- > curaggcontext = NULL;// see note * result = FunctionCallInvoke (fcinfo); / / call function * isnull = fcinfo- > isnull; / * * Make sure pass-by-ref data is allocated in the appropriate context. (We * need this in case the function returns a pointer into some short-lived * tuple, as is entirely possible.) * / if (! perfuncstate- > resulttypeByVal & &! fcinfo- > isnull & &! MemoryContextContains (CurrentMemoryContext, DatumGetPointer (* result) * result = datumCopy (* result Perfuncstate- > resulttypeByVal, perfuncstate- > resulttypeLen) / / see note MemoryContextSwitchTo (oldContext); / / switch back to the original context} is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.