首页 > 数据库 >Overview how does plpgsql function get executed

Overview how does plpgsql function get executed

时间:2024-01-30 20:11:07浏览次数:39  
标签:function executed get id SPI plan orders plpgsql

The SQL

CREATE FUNCTION process_orders()
RETURNS void AS $$
DECLARE 
   c record;
BEGIN
  FOR c IN SELECT * FROM orders LOOP
    RAISE NOTICE 'Processing: % %', c.id, c.total; 
  END LOOP;
END;
$$ LANGUAGE plpgsql;

create table orders(id int, total int);
-- if you don't insert some rows, statements
-- inside the `for` loop will not be handled by plpgsql.
insert into orders values(1, 10);

select process_orders();

Before Execution

On creating function process_orders, only syntax is checked for plpgsql body. If the function does not have any syntax errors(even the table does not exist on creating function), one row for process_orders is inserted into table pg_proc. Here's code path into syntax checking:

ProcessUtilitySlow // step in
CreateFunction
ProcedureCreate
OidFunctionCall1Coll
FunctionCall1Coll
// the following functions are defined inside `plpgsql` extension
plpgsql_validator
plpgsql_compile
do_compile
plpgsql_yyparse
**check_sql_expr**

A note regarding // step in: it suggests that the line following the current line is considered as the child of the current line. In this context, each function, placed on its own line, is executed from within the function listed above it.

plpgsql_yyparse processes plpgsql statements. In short words, it could extract sql related structure from the plpgsql statements. In this context, it extracts three sql expressions,

  1. SELECT * FROM orders
  2. c.id
  3. c.total

All of them are passed to check_sql_expr. If you want to quickly find how plpgsql statements are parsed by bison, you could add code here into pl_gram.y.

On Execution

when seelct process_orders() gets executed, it goes through the normal query processing flow:

  1. analyze
  2. rewrite // ignored here
  3. plan
  4. execute // ignored here

The first 3 steps are processed by SPI API SPI_prepare_extended.

analyze

For the expression c.id and c.total, plpgsql creates two select statements, i.e.

select c.id;
select c.total;

These two expressions are analyzed by function parse_analyze_withcb and finally are passed to function transformColumnRef. In contrast to the typical processing logic, the parsing logic of the two expressions is redirected back to the plpgsql extension through p_post_columnref_hook. In this context, the post hook function is resolve_column_ref inside which c.id gets resolved.

plan

After analyzing, plpgsql extension will finally invoke pg_plan_queries to generate the execution plan.

For select * from orders, plpgsql invokes SPI_cursor_open_with_paramlist,

exec_toplevel_block
exec_stmt_block
exec_stmts
exec_stmt_fors
exec_run_select
SPI_cursor_open_with_paramlist
SPI_cursor_open_internal
GetCachedPlan
BuildCachedPlan
pg_plan_queries

In this context, params passed to SPI_cursor_open_with_paramlist is NULL.

For c.id and c.total, plpgsql invokes SPI_plan_get_cached_plan.

标签:function,executed,get,id,SPI,plan,orders,plpgsql
From: https://www.cnblogs.com/lddcool/p/17997873

相关文章

  • close annoying "--no-check-certificate" of wget utility
    如果想使用alias:aliaswgetncc='wget--no-check-certificate'请往下看:Trythis:(assumes*nix)echo"check_certificate=off">>~/.wgetrcTheneverafter,wgetwillactlikeyouspecifiedthe--no-check-certificateswitch.Moreinfoat......
  • Qt 解决qtcreator工程文件例程报错error: cannot initialize object parameter of typ
    qt下载好并且环境配置完成,kits和qt都已配置完成在qtcreator中,在终端手动编译qmakemake都完全没问题,但是在qtcreator中却报错。即使是新建工程例程都报错。版本qt5.6.0qtcreator4.11.0报错main.cpp:96:error:cannotinitializeobjectparameteroftype‘QWidget’wi......
  • Qt cannot initialize object parameter of type ‘QWidget‘ with an expression of
    报错如图:qtcreator工程文件例程报错error:cannotinitializeobjectparameteroftype‘QWidget’withanexpressionoftype‘tab_workface’问题分析可能因为qtcreator4.11.0basedonqt5.12版本略微冲突导致。。问题解决帮助->关于插件,将ClangCo......
  • MySQL 系统变量 group_replication_get_communication_protocol
    MySQL系统变量group_replication_get_communication_protocol(MonJan2923:14:512024)[root@GreatSQL][(none)]>selectversion(),group_replication_get_communication_protocol();+-----------+------------------------------------------------+|version()|gr......
  • Eventgrid+Function实现event driven架构 - 架构介绍及环境部署
    今天来介绍这几年在云上比较流行的eventdriven,也就是事件驱动的架构,用一个很简单的sample来实际看下事件驱动的架构到底是个啥事件驱动的架构由生成事件流的事件生成者和侦听事件的事件使用者组成,它的特点是事件可几乎实时发送,因此使用者可在事件发生时需要立即做出响应。生成者......
  • python中get请求传参方式的写法
    get请求分为两大类:无参数和有参数1.无参数2.有参数2.1参数较少2.2参数较多-字典形式2.3参数较多-列表+元祖形式......
  • net8 随机数类Random GetItems() 、Shuffle()方法
    1、在8中对随机数类Random提供了GetItems()方法,可以根据指定的数量在提供的一个集合中随机抽取数据项生成一个新的集合:ReadOnlySpan<string>colors=new[]{"Red","Green","Blue","Black"};string[]t1=Random.Shared.GetItems(colors,10);Console.WriteLine(......
  • net8 对接webapi接口通过 GetFromJsonAsAsyncEnumerable方法直接得到对象,无需进行反序
    调用API直接获取到对象现在有一个接口返回如下图中的数据:如果是在8以前的版本中获取该接口的数据,需要先获取到接口内容,然后进行反序列化,代码如下conststringRequestUri="http://localhost:5145/user";usingvarclient=newHttpClient();varstream=awaitclient......
  • C. Did We Get Everything Covered
    原题链接前情提要限于自身知识水平的储备不足,无法对这道题的贪心算法做出一个证明,待来日学识渐长把这个证明写下题解我们可以把字符串s分成若干区间,每一区间对应一位数字的储备已知长度为n,那我们就一位一位地遍历,一旦所有元素遍历齐就开始下一位的遍历,因为再往后遍历也不起作......
  • 使用 NuGet.Server 创建和部署 ASP.NET Web 应用程序搭建私有Nuget服务器
    使用NuGet.Server创建和部署ASP.NETWeb应用程序搭建私有Nuget服务器在VisualStudio中,选择“新建>文件>”Project,搜索“ASP.NETWeb应用程序(.NETFramework)”,选择C#的匹配模板。将“框架”设置为“.NETFramework4.6”。为应用程序提供除NuGet.Server......