Sinsing Analysis on Pl/pgsql Grammar (i)

Recently in the study of PostgreSQL, so also has a strong interest in pl/pgsql, so here is a little summary, first of all, this PL is procedure language shorthand, that is, the so-called "process language." A large part of the following is quoted from the Pgsql manual, but it also makes some simple changes based on your own understanding.

first, the timing of the call

Where the Pl/pgsql function is called the first time, the source code within its function is parsed into a binary instruction tree, but there are some instructions for the expression inside the function, and only when it is first used, does pgsql give it an execution plan. And this execution plan will be used repeatedly in this session. Generally speaking, it is reasonable to do so, usually the use of a session is similar to the situation.

But doing so can be problematic, and that is, if we modify the corresponding database objects dynamically, it can cause problems. It is also important to note that if a partial SQL command or expression is not used in a conditional statement, then the PLPGSQL interpreter will not prepare the execution plan for it in this call.

second, the function creation syntax

Here is the format we usually create a function for:

CREATE FUNCTION populate () RETURNS integer as $$

DECLARE

-----declaring several variables

BEGIN

PERFORM my_function ();

END;

$$ LANGUAGE Plpgsql;

In the above format, the execution plan of the Perform statement will refer to the OID of the My_function object. If after this, if we rebuild the my_function () function, then the populate function will not be able to find the OID of the original my_function function.

To solve this kind of problem, we can choose to rebuild the populated function, or create a new session, allowing Pgsql to recompile the function. Usually we use the Create OR REPLACE function command when creating a function.

So we have to use the same table and field each time we execute the SQL command directly in the Plpgsql, that is, we cannot take the function's arguments as the table name or the field name of the SQL command. If we want to circumvent this restriction, we can use the EXECUTE statement in Plpgsql to build the command dynamically, and it will cause the problem that each execution requires a new command plan to be constructed.

A very important advantage of using the Plpgsql function is that it can improve the execution efficiency of the program, because the original SQL call needs to pass data repeatedly between the client and the server, which not only increases the overhead of interprocess communication, but also increases the overhead of network IO.

Thirdly, the basic structure

because Plpgsql is a block-structured language, all the text defined by a function must be written to a block, where each declaration and every statement within a block must end with a semicolon. Blocks can be nested, and the end keyword of the child block must be followed by a semicolon, but the semicolon can be omitted for the last end keyword of the function body.

Here is an example of a common format:

[<< label >>]

[DECLARE Variable declaration]

BEGIN

other statements;

END [Label];

In Plpgsql, there are two kinds of annotation styles, in which the double dash is--to denote a single line of comments, and/*/is a multiline comment, which draws on the multi-line annotation style in the C language.

It is important to note that variables defined in the declaration segment preceding the statement block initialize the declared variable to the default value each time it enters the statement block, rather than initializing it every time the function is called.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Sinsing Analysis on Pl/pgsql Grammar (i)