Postgres get diagnostics

Postgres get diagnostics DEFAULT

13.6.7.3 GET DIAGNOSTICS Statement

GET [CURRENT | STACKED] DIAGNOSTICS { [, ] ... | CONDITION [, ] ... } : = : = : { NUMBER | ROW_COUNT } : { CLASS_ORIGIN | SUBCLASS_ORIGIN | RETURNED_SQLSTATE | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME } , : (see following discussion)

SQL statements produce diagnostic information that populates the diagnostics area. The statement enables applications to inspect this information. (You can also use or to see conditions or errors.)

No special privileges are required to execute .

The keyword means to retrieve information from the current diagnostics area. The keyword means to retrieve information from the second diagnostics area, which is available only if the current context is a condition handler. If neither keyword is given, the default is to use the current diagnostics area.

The statement is typically used in a handler within a stored program. It is a MySQL extension that is permitted outside handler context to check the execution of any SQL statement. For example, if you invoke the mysql client program, you can enter these statements at the prompt:

mysql> ERROR 1051 (42S02): Unknown table 'test.no_such_table' mysql> mysql> +-------+------------------------------------+ | @p1 | @p2 | +-------+------------------------------------+ | 42S02 | Unknown table 'test.no_such_table' | +-------+------------------------------------+

This extension applies only to the current diagnostics area. It does not apply to the second diagnostics area because is permitted only if the current context is a condition handler. If that is not the case, a error occurs.

For a description of the diagnostics area, see Section 13.6.7.7, “The MySQL Diagnostics Area”. Briefly, it contains two kinds of information:

  • Statement information, such as the number of conditions that occurred or the affected-rows count.

  • Condition information, such as the error code and message. If a statement raises multiple conditions, this part of the diagnostics area has a condition area for each one. If a statement raises no conditions, this part of the diagnostics area is empty.

For a statement that produces three conditions, the diagnostics area contains statement and condition information like this:

Statement information: row count ... other statement information items ... Condition area list: Condition area 1: error code for condition 1 error message for condition 1 ... other condition information items ... Condition area 2: error code for condition 2: error message for condition 2 ... other condition information items ... Condition area 3: error code for condition 3 error message for condition 3 ... other condition information items ...

can obtain either statement or condition information, but not both in the same statement:

  • To obtain statement information, retrieve the desired statement items into target variables. This instance of assigns the number of available conditions and the rows-affected count to the user variables and :

    GET DIAGNOSTICS @p1 = NUMBER, @p2 = ROW_COUNT;
  • To obtain condition information, specify the condition number and retrieve the desired condition items into target variables. This instance of assigns the SQLSTATE value and error message to the user variables and :

    GET DIAGNOSTICS CONDITION 1 @p3 = RETURNED_SQLSTATE, @p4 = MESSAGE_TEXT;

The retrieval list specifies one or more assignments, separated by commas. Each assignment names a target variable and either a or designator, depending on whether the statement retrieves statement or condition information.

Valid designators for storing item information can be stored procedure or function parameters, stored program local variables declared with , or user-defined variables.

Valid designators can be stored procedure or function parameters, stored program local variables declared with , user-defined variables, system variables, or literals. A character literal may include a introducer. A warning occurs if the condition number is not in the range from 1 to the number of condition areas that have information. In this case, the warning is added to the diagnostics area without clearing it.

When a condition occurs, MySQL does not populate all condition items recognized by . For example:

mysql> mysql> +------+------+ | @p5 | @p6 | +------+------+ | | | +------+------+

In standard SQL, if there are multiple conditions, the first condition relates to the value returned for the previous SQL statement. In MySQL, this is not guaranteed. To get the main error, you cannot do this:

GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO;

Instead, retrieve the condition count first, then use it to specify which condition number to inspect:

GET DIAGNOSTICS @cno = NUMBER; GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;

For information about permissible statement and condition information items, and which ones are populated when a condition occurs, see Diagnostics Area Information Items.

Here is an example that uses and an exception handler in stored procedure context to assess the outcome of an insert operation. If the insert was successful, the procedure uses to get the rows-affected count. This shows that you can use multiple times to retrieve information about a statement as long as the current diagnostics area has not been cleared.

CREATE PROCEDURE do_insert(value INT) BEGIN -- Declare variables to hold diagnostics area information DECLARE code CHAR(5) DEFAULT '00000'; DECLARE msg TEXT; DECLARE nrows INT; DECLARE result TEXT; -- Declare exception handler for failed insert DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT; END; -- Perform the insert INSERT INTO t1 (int_col) VALUES(value); -- Check whether the insert was successful IF code = '00000' THEN GET DIAGNOSTICS nrows = ROW_COUNT; SET result = CONCAT('insert succeeded, row count = ',nrows); ELSE SET result = CONCAT('insert failed, error = ',code,', message = ',msg); END IF; -- Say what happened SELECT result; END;

Suppose that is an integer column that is declared as . The procedure produces these results when invoked to insert non- and values, respectively:

mysql> +---------------------------------+ | result | +---------------------------------+ | insert succeeded, row count = 1 | +---------------------------------+ mysql> +-------------------------------------------------------------------------+ | result | +-------------------------------------------------------------------------+ | insert failed, error = 23000, message = Column 'int_col' cannot be null | +-------------------------------------------------------------------------+

When a condition handler activates, a push to the diagnostics area stack occurs:

  • The first (current) diagnostics area becomes the second (stacked) diagnostics area and a new current diagnostics area is created as a copy of it.

  • and can be used within the handler to access the contents of the current and stacked diagnostics areas.

  • Initially, both diagnostics areas return the same result, so it is possible to get information from the current diagnostics area about the condition that activated the handler, as long as you execute no statements within the handler that change its current diagnostics area.

  • However, statements executing within the handler can modify the current diagnostics area, clearing and setting its contents according to the normal rules (see How the Diagnostics Area is Cleared and Populated).

    A more reliable way to obtain information about the handler-activating condition is to use the stacked diagnostics area, which cannot be modified by statements executing within the handler except . For information about when the current diagnostics area is set and cleared, see Section 13.6.7.7, “The MySQL Diagnostics Area”.

The next example shows how can be used within a handler to obtain information about the handled exception, even after the current diagnostics area has been modified by handler statements.

Within a stored procedure , we attempt to insert two values into a table that contains a column. The first value is a non- string and the second is . The column prohibits values, so the first insert succeeds but the second causes an exception. The procedure includes an exception handler that maps attempts to insert into inserts of the empty string:

When the handler activates, a copy of the current diagnostics area is pushed to the diagnostics area stack. The handler first displays the contents of the current and stacked diagnostics areas, which are both the same initially:

+---------------------------------+-------+----------------------------+ | op | errno | msg | +---------------------------------+-------+----------------------------+ | current DA before mapped insert | 1048 | Column 'c1' cannot be null | +---------------------------------+-------+----------------------------+ +---------------------------------+-------+----------------------------+ | op | errno | msg | +---------------------------------+-------+----------------------------+ | stacked DA before mapped insert | 1048 | Column 'c1' cannot be null | +---------------------------------+-------+----------------------------+

Statements executing after the statements may reset the current diagnostics area. statements may reset the current diagnostics area. For example, the handler maps the insert to an empty-string insert and displays the result. The new insert succeeds and clears the current diagnostics area, but the stacked diagnostics area remains unchanged and still contains information about the condition that activated the handler:

+----------------------------------------------+ | op | +----------------------------------------------+ | mapped insert succeeded, current DA is empty | +----------------------------------------------+ +--------------------------------+-------+----------------------------+ | op | errno | msg | +--------------------------------+-------+----------------------------+ | stacked DA after mapped insert | 1048 | Column 'c1' cannot be null | +--------------------------------+-------+----------------------------+

When the condition handler ends, its current diagnostics area is popped from the stack and the stacked diagnostics area becomes the current diagnostics area in the stored procedure.

After the procedure returns, the table contains two rows. The empty row results from the attempt to insert that was mapped to an empty-string insert:

+----------+ | c1 | +----------+ | string 1 | | | +----------+

In the preceding example, the first two statements within the condition handler that retrieve information from the current and stacked diagnostics areas return the same values. This is not the case if statements that reset the current diagnostics area executed earlier within the handler. Suppose that is rewritten to place the statements within the handler definition rather than preceding it:

CREATE PROCEDURE p () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN -- Declare variables to hold diagnostics area information DECLARE errcount INT; DECLARE errno INT; DECLARE msg TEXT; GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT; SELECT 'current DA before mapped insert' AS op, errno, msg; GET STACKED DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT; SELECT 'stacked DA before mapped insert' AS op, errno, msg; ...

In this case, the result is version dependent:

  • Before MySQL 5.7.2, does not change the current diagnostics area, so the first two statements return the same result, just as in the original version of .

    In MySQL 5.7.2, work was done to ensure that all nondiagnostic statements populate the diagnostics area, per the SQL standard. is one of them, so in 5.7.2 and higher, statements executing at the beginning of the handler clear the current diagnostics area and the statements produce different results:

    +---------------------------------+-------+------+ | op | errno | msg | +---------------------------------+-------+------+ | current DA before mapped insert | NULL | NULL | +---------------------------------+-------+------+ +---------------------------------+-------+----------------------------+ | op | errno | msg | +---------------------------------+-------+----------------------------+ | stacked DA before mapped insert | 1048 | Column 'c1' cannot be null | +---------------------------------+-------+----------------------------+

To avoid this issue within a condition handler when seeking to obtain information about the condition that activated the handler, be sure to access the stacked diagnostics area, not the current diagnostics area.

Sours: https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/get-diagnostics.html
Now GET DIAGNOSTIC will return the number of rows processed by COPY statement in a Pl/Pgsql function.
COPY statement in Pl/Pgsql Function:CREATE OR REPLACE FUNCTION public.copy_data(fname text) RETURNS integer
AS
$$
declare
copy_qry text;
cnt integer;
Begin
copy_qry := 'copy t from'||quote_literal(fname)||' with CSV HEADER;';
Execute copy_qry;
GET DIAGNOSTICS cnt = ROW_COUNT;
return cnt;
end;
$$ Language plpgsql;

Previous release:

-bash-4.1$ psql
psql.bin (9.2.3)
Type "help" for help.

postgres=# select copy_data('/usr/local/pg93beta/t_load.csv');
copy_data
-----------
0
(1 row)

In PostgreSQL 9.3

-bash-4.1$ ./psql -p 5555
psql (9.3beta1)
Type "help" for help.

postgres=# select copy_data('/usr/local/pg93beta/t_load.csv');
copy_data
-----------
3
(1 row)
or
-bash-4.1$ ./psql -p 5555 -c "select copy_data('/usr/local/pg93beta/t_load.csv');"
copy_data
-----------
3
(1 row)

Thanks to author, it looks simple but very effective when working with loading the data using scripts and want to know the number of rows processed by the COPY statement.

–Raghav

Sours: https://raghavt.blog/get-diagnostics-with-copy-statement-in-pl-pgsql-function/
  1. Econo rio grande
  2. Marvel comics stamps
  3. Sweet hs code
  4. Ford procharger
  5. Empty coloring pages

37.6. Basic Statements

In this section and the following ones, we describe all the statement types that are explicitly understood by PL/pgSQL. Anything not recognized as one of these statement types is presumed to be an SQL command and is sent to the main database engine to execute (after substitution of any PL/pgSQL variables used in the statement). Thus, for example, the SQL commands , , and may be considered to be statements of PL/pgSQL, but they are not specifically listed here.

37.6.1. Assignment

An assignment of a value to a variable or row/record field is written as:

:= ;

As explained above, the expression in such a statement is evaluated by means of an SQL command sent to the main database engine. The expression must yield a single value.

If the expression's result data type doesn't match the variable's data type, or the variable has a specific size/precision (like ), the result value will be implicitly converted by the PL/pgSQL interpreter using the result type's output-function and the variable type's input-function. Note that this could potentially result in run-time errors generated by the input function, if the string form of the result value is not acceptable to the input function.

Examples:

user_id := 20; tax := subtotal * 0.06;

37.6.2.

The result of a command yielding multiple columns (but only one row) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by:

SELECT INTO FROM ...;

where can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields. The and the remainder of the command are the same as in regular SQL.

Note that this is quite different from PostgreSQL's normal interpretation of , where the target is a newly created table. If you want to create a table from a result inside a PL/pgSQL function, use the syntax .

If a row or a variable list is used as target, the selected values must exactly match the structure of the target, or a run-time error occurs. When a record variable is the target, it automatically configures itself to the row type of the query result columns.

Except for the clause, the statement is the same as a normal SQL command and can use its full power.

If the query returns zero rows, null values are assigned to the target(s). If the query returns multiple rows, the first row is assigned to the target(s) and the rest are discarded. (Note that "the first row" is not well-defined unless you've used .)

At present, the clause can appear almost anywhere in the statement, but it is recommended to place it immediately after the key word as depicted above. Future versions of PL/pgSQL may be less forgiving about placement of the clause.

You can use immediately after a statement to determine whether the assignment was successful (that is, at least one row was was returned by the query). For example:

SELECT INTO myrec * FROM emp WHERE empname = myname; IF NOT FOUND THEN RAISE EXCEPTION ''employee % not found'', myname; END IF;

To test for whether a record/row result is null, you can use the conditional. There is, however, no way to tell whether any additional rows might have been discarded. Here is an example that handles the case where no rows have been returned:

DECLARE users_rec RECORD; full_name varchar; BEGIN SELECT INTO users_rec * FROM users WHERE user_id=3; IF users_rec.homepage IS NULL THEN -- user entered no homepage, return "http://" RETURN ''http://''; END IF; END;

37.6.3. Executing an Expression or Query With No Result

Sometimes one wishes to evaluate an expression or query but discard the result (typically because one is calling a function that has useful side-effects but no useful result value). To do this in PL/pgSQL, use the statement:

PERFORM ;

This executes , which must be a statement, and discards the result. PL/pgSQL variables are substituted in the query as usual. Also, the special variable is set to true if the query produced at least one row or false if it produced no rows.

Note: One might expect that with no clause would accomplish this result, but at present the only accepted way to do it is .

An example:

PERFORM create_mv(''cs_session_page_requests_mv'', my_query);

37.6.4. Executing Dynamic Commands

Oftentimes you will want to generate dynamic commands inside your PL/pgSQL functions, that is, commands that will involve different tables or different data types each time they are executed. PL/pgSQL's normal attempts to cache plans for commands will not work in such scenarios. To handle this sort of problem, the statement is provided:

EXECUTE ;

where is an expression yielding a string (of type ) containing the command to be executed. This string is fed literally to the SQL engine.

Note in particular that no substitution of PL/pgSQL variables is done on the command string. The values of variables must be inserted in the command string as it is constructed.

When working with dynamic commands you will have to face escaping of single quotes in PL/pgSQL. Please refer to the overview in Section 37.2.1, which can save you some effort.

Unlike all other commands in PL/pgSQL, a command run by an statement is not prepared and saved just once during the life of the session. Instead, the command is prepared each time the statement is run. The command string can be dynamically created within the function to perform actions on variable tables and columns.

The results from commands are discarded by , and is not currently supported within . There are two ways to extract a result from a dynamically-created : one is to use the loop form described in Section 37.7.4, and the other is to use a cursor with , as described in Section 37.8.2.

An example:

EXECUTE ''UPDATE tbl SET '' || quote_ident(colname) || '' = '' || quote_literal(newvalue) || '' WHERE ...'';

This example shows use of the functions and . For safety, variables containing column and table identifiers should be passed to function . Variables containing values that should be literal strings in the constructed command should be passed to . Both take the appropriate steps to return the input text enclosed in double or single quotes respectively, with any embedded special characters properly escaped.

Here is a much larger example of a dynamic command and :

CREATE FUNCTION cs_update_referrer_type_proc() RETURNS integer AS ' DECLARE referrer_keys RECORD; -- declare a generic record to be used in a FOR a_output varchar(4000); BEGIN a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar, varchar, varchar) RETURNS varchar AS '''' DECLARE v_host ALIAS FOR $1; v_domain ALIAS FOR $2; v_url ALIAS FOR $3; BEGIN ''; -- Notice how we scan through the results of a query in a FOR loop -- using the FOR <record> construct. FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE '''''''''' || referrer_keys.key_string || '''''''''' THEN RETURN '''''' || referrer_keys.referrer_type || ''''''; END IF;''; END LOOP; a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE plpgsql;''; EXECUTE a_output; END; ' LANGUAGE plpgsql;

37.6.5. Obtaining the Result Status

There are several ways to determine the effect of a command. The first method is to use the command, which has the form:

GET DIAGNOSTICS = [ , ... ] ;

This command allows retrieval of system status indicators. Each is a key word identifying a state value to be assigned to the specified variable (which should be of the right data type to receive it). The currently available status items are , the number of rows processed by the last command sent down to the engine, and , the OID of the last row inserted by the most recent command. Note that is only useful after an command.

An example:

GET DIAGNOSTICS integer_var = ROW_COUNT;

The second method to determine the effects of a command is to check the special variable named , which is of type . starts out false within each PL/pgSQL function call. It is set by each of the following types of statements:

  • A statement sets true if it returns a row, false if no row is returned.

  • A statement sets true if it produces (and discards) a row, false if no row is produced.

  • , , and statements set true if at least one row is affected, false if no row is affected.

  • A statement sets true if it returns a row, false if no row is returned.

  • A statement sets true if it iterates one or more times, else false. This applies to all three variants of the statement (integer loops, record-set loops, and dynamic record-set loops). is only set when the loop exits: inside the execution of the loop, is not modified by the statement, although it may be changed by the execution of other statements within the loop body.

is a local variable; any changes to it affect only the current PL/pgSQL function.

Sours: http://aturing.umcs.maine.edu/~sudarshan.chawathe/200909/cos480/pgsql/doc/html/plpgsql-statements.html
Databases: How to get row_count diagnostic after EXPLAIN ANALYZE in Postgres? (3 Solutions!!)

39.5. Basic Statements

In this section and the following ones, we describe all the statement types that are explicitly understood by PL/pgSQL. Anything not recognized as one of these statement types is presumed to be an SQL command and is sent to the main database engine to execute, as described in Section 39.5.2 and Section 39.5.3.

39.5.1. Assignment

An assignment of a value to a PL/pgSQL variable is written as:

:= ;

As explained previously, the expression in such a statement is evaluated by means of an SQL command sent to the main database engine. The expression must yield a single value (possibly a row value, if the variable is a row or record variable). The target variable can be a simple variable (optionally qualified with a block name), a field of a row or record variable, or an element of an array that is a simple variable or field.

If the expression's result data type doesn't match the variable's data type, or the variable has a specific size/precision (like ), the result value will be implicitly converted by the PL/pgSQL interpreter using the result type's output-function and the variable type's input-function. Note that this could potentially result in run-time errors generated by the input function, if the string form of the result value is not acceptable to the input function.

Examples:

tax := subtotal * 0.06; my_record.user_id := 20;

39.5.2. Executing a Command With No Result

For any SQL command that does not return rows, for example without a clause, you can execute the command within a PL/pgSQL function just by writing the command.

Any PL/pgSQL variable name appearing in the command text is treated as a parameter, and then the current value of the variable is provided as the parameter value at run time. This is exactly like the processing described earlier for expressions; for details see Section 39.10.1.

When executing a SQL command in this way, PL/pgSQL plans the command just once and re-uses the plan on subsequent executions, for the life of the database connection. The implications of this are discussed in detail in Section 39.10.2.

Sometimes it is useful to evaluate an expression or query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the statement:

PERFORM ;

This executes and discards the result. Write the the same way you would write an SQL command, but replace the initial keyword with . For queries, use and then place the query in parentheses. (In this case, the query can only return one row.) PL/pgSQL variables will be substituted into the query just as for commands that return no result, and the plan is cached in the same way. Also, the special variable is set to true if the query produced at least one row, or false if it produced no rows (see Section 39.5.5).

Note: One might expect that writing directly would accomplish this result, but at present the only accepted way to do it is . A SQL command that can return rows, such as , will be rejected as an error unless it has an clause as discussed in the next section.

An example:

PERFORM create_mv('cs_session_page_requests_mv', my_query);

39.5.3. Executing a Query with a Single-Row Result

The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding an clause. For example,

SELECT INTO [STRICT] FROM ...; INSERT ... RETURNING INTO [STRICT] ; UPDATE ... RETURNING INTO [STRICT] ; DELETE ... RETURNING INTO [STRICT] ;

where can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields. PL/pgSQL variables will be substituted into the rest of the query, and the plan is cached, just as described above for commands that do not return rows. This works for , // with , and utility commands that return row-set results (such as ). Except for the clause, the SQL command is the same as it would be written outside PL/pgSQL.

Tip: Note that this interpretation of with is quite different from PostgreSQL's regular command, wherein the target is a newly created table. If you want to create a table from a result inside a PL/pgSQL function, use the syntax .

If a row or a variable list is used as target, the query's result columns must exactly match the structure of the target as to number and data types, or else a run-time error occurs. When a record variable is the target, it automatically configures itself to the row type of the query result columns.

The clause can appear almost anywhere in the SQL command. Customarily it is written either just before or just after the list of in a command, or at the end of the command for other command types. It is recommended that you follow this convention in case the PL/pgSQL parser becomes stricter in future versions.

If is not specified in the clause, then will be set to the first row returned by the query, or to nulls if the query returned no rows. (Note that "the first row" is not well-defined unless you've used .) Any result rows after the first row are discarded. You can check the special variable (see Section 39.5.5) to determine whether a row was returned:

SELECT * INTO myrec FROM emp WHERE empname = myname; IF NOT FOUND THEN RAISE EXCEPTION 'employee % not found', myname; END IF;

If the option is specified, the query must return exactly one row or a run-time error will be reported, either (no rows) or (more than one row). You can use an exception block if you wish to catch the error, for example:

BEGIN SELECT * INTO STRICT myrec FROM emp WHERE empname = myname; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'employee % not found', myname; WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION 'employee % not unique', myname; END;

Successful execution of a command with always sets to true.

For // with , PL/pgSQL reports an error for more than one returned row, even when is not specified. This is because there is no option such as with which to determine which affected row should be returned.

Note: The option matches the behavior of Oracle PL/SQL's and related statements.

To handle cases where you need to process multiple result rows from a SQL query, see Section 39.6.4.

39.5.4. Executing Dynamic Commands

Oftentimes you will want to generate dynamic commands inside your PL/pgSQL functions, that is, commands that will involve different tables or different data types each time they are executed. PL/pgSQL's normal attempts to cache plans for commands (as discussed in Section 39.10.2) will not work in such scenarios. To handle this sort of problem, the statement is provided:

EXECUTE [ INTO [STRICT] ] [ USING [, ... ] ];

where is an expression yielding a string (of type ) containing the command to be executed. The optional is a record variable, a row variable, or a comma-separated list of simple variables and record/row fields, into which the results of the command will be stored. The optional expressions supply values to be inserted into the command.

No substitution of PL/pgSQL variables is done on the computed command string. Any required variable values must be inserted in the command string as it is constructed; or you can use parameters as described below.

Also, there is no plan caching for commands executed via . Instead, the command is prepared each time the statement is run. Thus the command string can be dynamically created within the function to perform actions on different tables and columns.

The clause specifies where the results of a SQL command returning rows should be assigned. If a row or variable list is provided, it must exactly match the structure of the query's results (when a record variable is used, it will configure itself to match the result structure automatically). If multiple rows are returned, only the first will be assigned to the variable. If no rows are returned, NULL is assigned to the variable(s). If no clause is specified, the query results are discarded.

If the option is given, an error is reported unless the query produces exactly one row.

The command string can use parameter values, which are referenced in the command as , , etc. These symbols refer to values supplied in the clause. This method is often preferable to inserting data values into the command string as text: it avoids run-time overhead of converting the values to text and back, and it is much less prone to SQL-injection attacks since there is no need for quoting or escaping. An example is:

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date;

Note that parameter symbols can only be used for data values — if you want to use dynamically determined table or column names, you must insert them into the command string textually. For example, if the preceding query needed to be done against a dynamically selected table, you could do this:

EXECUTE 'SELECT count(*) FROM ' || tabname::regclass || ' WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date;

Another restriction on parameter symbols is that they only work in , , , and commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values.

An with a simple constant command string and some parameters, as in the first example above, is functionally equivalent to just writing the command directly in PL/pgSQL and allowing replacement of PL/pgSQL variables to happen automatically. The important difference is that will re-plan the command on each execution, generating a plan that is specific to the current parameter values; whereas PL/pgSQL normally creates a generic plan and caches it for re-use. In situations where the best plan depends strongly on the parameter values, can be significantly faster; while when the plan is not sensitive to parameter values, re-planning will be a waste.

is not currently supported within ; instead, execute a plain command and specify as part of the itself.

Note: The PL/pgSQL statement is not related to the EXECUTE SQL statement supported by the PostgreSQL server. The server's statement cannot be used directly within PL/pgSQL functions (and is not needed).

Example 39-1. Quoting values in dynamic queries

When working with dynamic commands you will often have to handle escaping of single quotes. The recommended method for quoting fixed text in your function body is dollar quoting. (If you have legacy code that does not use dollar quoting, please refer to the overview in Section 39.11.1, which can save you some effort when translating said code to a more reasonable scheme.)

Dynamic values that are to be inserted into the constructed query require careful handling since they might themselves contain quote characters. An example (this assumes that you are using dollar quoting for the function as a whole, so the quote marks need not be doubled):

EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = ' || quote_literal(keyvalue);

This example demonstrates the use of the and functions (see Section 9.4). For safety, expressions containing column or table identifiers should be passed through before insertion in a dynamic query. Expressions containing values that should be literal strings in the constructed command should be passed through . These functions take the appropriate steps to return the input text enclosed in double or single quotes respectively, with any embedded special characters properly escaped.

Because is labelled , it will always return null when called with a null argument. In the above example, if or were null, the entire dynamic query string would become null, leading to an error from . You can avoid this problem by using the function, which works the same as except that when called with a null argument it returns the string . For example,

EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_nullable(newvalue) || ' WHERE key = ' || quote_nullable(keyvalue);

If you are dealing with values that might be null, you should usually use in place of .

As always, care must be taken to ensure that null values in a query do not deliver unintended results. For example the clause

'WHERE key = ' || quote_nullable(keyvalue)

will never succeed if is null, because the result of using the equality operator with a null operand is always null. If you wish null to work like an ordinary key value, you would need to rewrite the above as

'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)

(At present, is handled much less efficiently than , so don't do this unless you must. See Section 9.2 for more information on nulls and .)

Note that dollar quoting is only useful for quoting fixed text. It would be a very bad idea to try to write this example as:

EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = $$' || newvalue || '$$ WHERE key = ' || quote_literal(keyvalue);

because it would break if the contents of happened to contain . The same objection would apply to any other dollar-quoting delimiter you might pick. So, to safely quote text that is not known in advance, you must use , , or , as appropriate.

A much larger example of a dynamic command and can be seen in Example 39-7, which builds and executes a command to define a new function.

39.5.5. Obtaining the Result Status

There are several ways to determine the effect of a command. The first method is to use the command, which has the form:

GET DIAGNOSTICS = [ , ... ];

This command allows retrieval of system status indicators. Each is a key word identifying a state value to be assigned to the specified variable (which should be of the right data type to receive it). The currently available status items are , the number of rows processed by the last command sent to the engine, and , the OID of the last row inserted by the most recent command. Note that is only useful after an command into a table containing OIDs.

An example:

GET DIAGNOSTICS integer_var = ROW_COUNT;

The second method to determine the effects of a command is to check the special variable named , which is of type . starts out false within each PL/pgSQL function call. It is set by each of the following types of statements:

  • A statement sets true if a row is assigned, false if no row is returned.

  • A statement sets true if it produces (and discards) one or more rows, false if no row is produced.

  • , , and statements set true if at least one row is affected, false if no row is affected.

  • A statement sets true if it returns a row, false if no row is returned.

  • A statement sets true if it successfully repositions the cursor, false otherwise.

  • A statement sets true if it iterates one or more times, else false. This applies to all four variants of the statement (integer loops, record-set loops, dynamic record-set loops, and cursor loops). is set this way when the loop exits; inside the execution of the loop, is not modified by the statement, although it might be changed by the execution of other statements within the loop body.

  • and statements set true if the query returns at least one row, false if no row is returned.

Other PL/pgSQL statements do not change the state of . Note in particular that changes the output of , but does not change .

is a local variable within each PL/pgSQL function; any changes to it affect only the current function.

39.5.6. Doing Nothing At All

Sometimes a placeholder statement that does nothing is useful. For example, it can indicate that one arm of an if/then/else chain is deliberately empty. For this purpose, use the statement:

NULL;

For example, the following two fragments of code are equivalent:

BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN NULL; -- ignore the error END; BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN -- ignore the error END;

Which is preferable is a matter of taste.

Note: In Oracle's PL/SQL, empty statement lists are not allowed, and so statements are required for situations such as this. PL/pgSQL allows you to just write nothing, instead.

Sours: https://www.postgresql.org/docs/9.0/plpgsql-statements.html

Get diagnostics postgres

40.6. Control Structures

Control structures are probably the most useful (and important) part of PL/pgSQL. With PL/pgSQL's control structures, you can manipulate PostgreSQL data in a very flexible and powerful way.

40.6.1. Returning From a Function

There are two commands available that allow you to return data from a function: and .

40.6.1.1.

RETURN ;

with an expression terminates the function and returns the value of to the caller. This form is used for PL/pgSQL functions that do not return a set.

In a function that returns a scalar type, the expression's result will automatically be cast into the function's return type as described for assignments. But to return a composite (row) value, you must write an expression delivering exactly the requested column set. This may require use of explicit casting.

If you declared the function with output parameters, write just with no expression. The current values of the output parameter variables will be returned.

If you declared the function to return , a statement can be used to exit the function early; but do not write an expression following .

The return value of a function cannot be left undefined. If control reaches the end of the top-level block of the function without hitting a statement, a run-time error will occur. This restriction does not apply to functions with output parameters and functions returning , however. In those cases a statement is automatically executed if the top-level block finishes.

Some examples:

-- functions returning a scalar type RETURN 1 + 2; RETURN scalar_var; -- functions returning a composite type RETURN composite_type_var; RETURN (1, 2, 'three'::text); -- must cast columns to correct types

40.6.1.2. and

RETURN NEXT ; RETURN QUERY ; RETURN QUERY EXECUTE [ USING [, ... ] ];

When a PL/pgSQL function is declared to return , the procedure to follow is slightly different. In that case, the individual items to return are specified by a sequence of or commands, and then a final command with no argument is used to indicate that the function has finished executing. can be used with both scalar and composite data types; with a composite result type, an entire "table" of results will be returned. appends the results of executing a query to the function's result set. and can be freely intermixed in a single set-returning function, in which case their results will be concatenated.

and do not actually return from the function — they simply append zero or more rows to the function's result set. Execution then continues with the next statement in the PL/pgSQL function. As successive or commands are executed, the result set is built up. A final , which should have no argument, causes control to exit the function (or you can just let control reach the end of the function).

has a variant , which specifies the query to be executed dynamically. Parameter expressions can be inserted into the computed query string via , in just the same way as in the command.

If you declared the function with output parameters, write just with no expression. On each execution, the current values of the output parameter variable(s) will be saved for eventual return as a row of the result. Note that you must declare the function as returning when there are multiple output parameters, or when there is just one output parameter of type , in order to create a set-returning function with output parameters.

Here is an example of a function using :

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT); INSERT INTO foo VALUES (1, 2, 'three'); INSERT INTO foo VALUES (4, 5, 'six'); CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS $BODY$ DECLARE r foo%rowtype; BEGIN FOR r IN SELECT * FROM foo WHERE fooid > 0 LOOP -- can do some processing here RETURN NEXT r; -- return current row of SELECT END LOOP; RETURN; END $BODY$ LANGUAGE plpgsql; SELECT * FROM get_all_foo();

Here is an example of a function using :

CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS $BODY$ BEGIN RETURN QUERY SELECT flightid FROM flight WHERE flightdate >= $1 AND flightdate < ($1 + 1); -- Since execution is not finished, we can check whether rows were returned -- and raise exception if not. IF NOT FOUND THEN RAISE EXCEPTION 'No flight at %.', $1; END IF; RETURN; END $BODY$ LANGUAGE plpgsql; -- Returns available flights or raises exception if there are no -- available flights. SELECT * FROM get_available_flightid(CURRENT_DATE);

Note: The current implementation of and stores the entire result set before returning from the function, as discussed above. That means that if a PL/pgSQL function produces a very large result set, performance might be poor: data will be written to disk to avoid memory exhaustion, but the function itself will not return until the entire result set has been generated. A future version of PL/pgSQL might allow users to define set-returning functions that do not have this limitation. Currently, the point at which data begins being written to disk is controlled by the work_mem configuration variable. Administrators who have sufficient memory to store larger result sets in memory should consider increasing this parameter.

40.6.2. Conditionals

and statements let you execute alternative commands based on certain conditions. PL/pgSQL has three forms of :

and two forms of :

40.6.2.1.

IF THEN END IF;

statements are the simplest form of . The statements between and will be executed if the condition is true. Otherwise, they are skipped.

Example:

IF v_user_id <> 0 THEN UPDATE users SET email = v_email WHERE user_id = v_user_id; END IF;

40.6.2.2.

IF THEN ELSE END IF;

statements add to by letting you specify an alternative set of statements that should be executed if the condition is not true. (Note this includes the case where the condition evaluates to NULL.)

Examples:

IF parentid IS NULL OR parentid = '' THEN RETURN fullname; ELSE RETURN hp_true_filename(parentid) || '/' || fullname; END IF;IF v_count > 0 THEN INSERT INTO users_count (count) VALUES (v_count); RETURN 't'; ELSE RETURN 'f'; END IF;

40.6.2.3.

IF THEN [ ELSIF THEN [ ELSIF THEN ...]] [ ELSE ] END IF;

Sometimes there are more than just two alternatives. provides a convenient method of checking several alternatives in turn. The conditions are tested successively until the first one that is true is found. Then the associated statement(s) are executed, after which control passes to the next statement after . (Any subsequent conditions are not tested.) If none of the conditions is true, then the block (if any) is executed.

Here is an example:

IF number = 0 THEN result := 'zero'; ELSIF number > 0 THEN result := 'positive'; ELSIF number < 0 THEN result := 'negative'; ELSE -- hmm, the only other possibility is that number is null result := 'NULL'; END IF;

The key word can also be spelled .

An alternative way of accomplishing the same task is to nest statements, as in the following example:

IF demo_row.sex = 'm' THEN pretty_sex := 'man'; ELSE IF demo_row.sex = 'f' THEN pretty_sex := 'woman'; END IF; END IF;

However, this method requires writing a matching for each , so it is much more cumbersome than using when there are many alternatives.

40.6.2.4. Simple

CASE WHEN [, [ ... ]] THEN [ WHEN [, [ ... ]] THEN ... ] [ ELSE ] END CASE;

The simple form of provides conditional execution based on equality of operands. The is evaluated (once) and successively compared to each in the clauses. If a match is found, then the corresponding are executed, and then control passes to the next statement after . (Subsequent expressions are not evaluated.) If no match is found, the are executed; but if is not present, then a exception is raised.

Here is a simple example:

CASE x WHEN 1, 2 THEN msg := 'one or two'; ELSE msg := 'other value than one or two'; END CASE;

40.6.2.5. Searched

CASE WHEN THEN [ WHEN THEN ... ] [ ELSE ] END CASE;

The searched form of provides conditional execution based on truth of Boolean expressions. Each clause's is evaluated in turn, until one is found that yields . Then the corresponding are executed, and then control passes to the next statement after . (Subsequent expressions are not evaluated.) If no true result is found, the are executed; but if is not present, then a exception is raised.

Here is an example:

CASE WHEN x BETWEEN 0 AND 10 THEN msg := 'value is between zero and ten'; WHEN x BETWEEN 11 AND 20 THEN msg := 'value is between eleven and twenty'; END CASE;

This form of is entirely equivalent to , except for the rule that reaching an omitted clause results in an error rather than doing nothing.

40.6.3. Simple Loops

With the , , , , , and statements, you can arrange for your PL/pgSQL function to repeat a series of commands.

40.6.3.1.

[ <<>> ] LOOP END LOOP [];

defines an unconditional loop that is repeated indefinitely until terminated by an or statement. The optional can be used by and statements within nested loops to specify which loop those statements refer to.

40.6.3.2.

EXIT [] [ WHEN ];

If no is given, the innermost loop is terminated and the statement following is executed next. If is given, it must be the label of the current or some outer level of nested loop or block. Then the named loop or block is terminated and control continues with the statement after the loop's/block's corresponding .

If is specified, the loop exit occurs only if is true. Otherwise, control passes to the statement after .

can be used with all types of loops; it is not limited to use with unconditional loops.

When used with a block, passes control to the next statement after the end of the block. Note that a label must be used for this purpose; an unlabeled is never considered to match a block. (This is a change from pre-8.4 releases of PostgreSQL, which would allow an unlabeled to match a block.)

Examples:

LOOP -- some computations IF count > 0 THEN EXIT; -- exit loop END IF; END LOOP; LOOP -- some computations EXIT WHEN count > 0; -- same result as previous example END LOOP; <<ablock>> BEGIN -- some computations IF stocks > 100000 THEN EXIT ablock; -- causes exit from the BEGIN block END IF; -- computations here will be skipped when stocks > 100000 END;

40.6.3.3.

CONTINUE [] [ WHEN ];

If no is given, the next iteration of the innermost loop is begun. That is, all statements remaining in the loop body are skipped, and control returns to the loop control expression (if any) to determine whether another loop iteration is needed. If is present, it specifies the label of the loop whose execution will be continued.

If is specified, the next iteration of the loop is begun only if is true. Otherwise, control passes to the statement after .

can be used with all types of loops; it is not limited to use with unconditional loops.

Examples:

LOOP -- some computations EXIT WHEN count > 100; CONTINUE WHEN count < 50; -- some computations for count IN [50 .. 100] END LOOP;

40.6.3.4.

[ <<>> ] WHILE LOOP END LOOP [];

The statement repeats a sequence of statements so long as the evaluates to true. The expression is checked just before each entry to the loop body.

For example:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP -- some computations here END LOOP; WHILE NOT done LOOP -- some computations here END LOOP;

40.6.3.5. (Integer Variant)

[ <<>> ] FOR IN [ REVERSE ] .. [ BY ] LOOP END LOOP [];

This form of creates a loop that iterates over a range of integer values. The variable is automatically defined as type and exists only inside the loop (any existing definition of the variable name is ignored within the loop). The two expressions giving the lower and upper bound of the range are evaluated once when entering the loop. If the clause isn't specified the iteration step is 1, otherwise it's the value specified in the clause, which again is evaluated once on loop entry. If is specified then the step value is subtracted, rather than added, after each iteration.

Some examples of integer loops:

FOR i IN 1..10 LOOP -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop END LOOP; FOR i IN REVERSE 10..1 LOOP -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop END LOOP; FOR i IN REVERSE 10..1 BY 2 LOOP -- i will take on the values 10,8,6,4,2 within the loop END LOOP;

If the lower bound is greater than the upper bound (or less than, in the case), the loop body is not executed at all. No error is raised.

If a is attached to the loop then the integer loop variable can be referenced with a qualified name, using that .

40.6.4. Looping Through Query Results

Using a different type of loop, you can iterate through the results of a query and manipulate that data accordingly. The syntax is:

[ <<>> ] FOR IN LOOP END LOOP [];

The is a record variable, row variable, or comma-separated list of scalar variables. The is successively assigned each row resulting from the and the loop body is executed for each row. Here is an example:

CREATE FUNCTION refresh_mviews() RETURNS integer AS $$ DECLARE mviews RECORD; BEGIN RAISE NOTICE 'Refreshing all materialized views...'; FOR mviews IN SELECT n.nspname AS mv_schema, c.relname AS mv_name, pg_catalog.pg_get_userbyid(c.relowner) AS owner FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace) WHERE c.relkind = 'm' ORDER BY 1 LOOP -- Now "mviews" has one record with information about the materialized view RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...', quote_ident(mviews.mv_schema), quote_ident(mviews.mv_name), quote_ident(mviews.owner); EXECUTE 'REFRESH MATERIALIZED VIEW ' || quote_ident(mviews.mv_schema) || '.' || quote_ident(mviews.mv_name); END LOOP; RAISE NOTICE 'Done refreshing materialized views.'; RETURN 1; END; $$ LANGUAGE plpgsql;

If the loop is terminated by an statement, the last assigned row value is still accessible after the loop.

The used in this type of statement can be any SQL command that returns rows to the caller: is the most common case, but you can also use , , or with a clause. Some utility commands such as will work too.

PL/pgSQL variables are substituted into the query text, and the query plan is cached for possible re-use, as discussed in detail in Section 40.10.1 and Section 40.10.2.

The statement is another way to iterate over rows:

[ <<>> ] FOR IN EXECUTE [ USING [, ... ] ] LOOP END LOOP [];

This is like the previous form, except that the source query is specified as a string expression, which is evaluated and replanned on each entry to the loop. This allows the programmer to choose the speed of a preplanned query or the flexibility of a dynamic query, just as with a plain statement. As with , parameter values can be inserted into the dynamic command via .

Another way to specify the query whose results should be iterated through is to declare it as a cursor. This is described in Section 40.7.4.

40.6.5. Looping Through Arrays

The loop is much like a loop, but instead of iterating through the rows returned by a SQL query, it iterates through the elements of an array value. (In general, is meant for looping through components of a composite-valued expression; variants for looping through composites besides arrays may be added in future.) The statement to loop over an array is:

[ <<>> ] FOREACH [ SLICE ] IN ARRAY LOOP END LOOP [];

Without , or if is specified, the loop iterates through individual elements of the array produced by evaluating the . The variable is assigned each element value in sequence, and the loop body is executed for each element. Here is an example of looping through the elements of an integer array:

CREATE FUNCTION sum(int[]) RETURNS int8 AS $$ DECLARE s int8 := 0; x int; BEGIN FOREACH x IN ARRAY $1 LOOP s := s + x; END LOOP; RETURN s; END; $$ LANGUAGE plpgsql;

The elements are visited in storage order, regardless of the number of array dimensions. Although the is usually just a single variable, it can be a list of variables when looping through an array of composite values (records). In that case, for each array element, the variables are assigned from successive columns of the composite value.

With a positive value, iterates through slices of the array rather than single elements. The value must be an integer constant not larger than the number of dimensions of the array. The variable must be an array, and it receives successive slices of the array value, where each slice is of the number of dimensions specified by . Here is an example of iterating through one-dimensional slices:

CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$ DECLARE x int[]; BEGIN FOREACH x SLICE 1 IN ARRAY $1 LOOP RAISE NOTICE 'row = %', x; END LOOP; END; $$ LANGUAGE plpgsql; SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]); NOTICE: row = {1,2,3} NOTICE: row = {4,5,6} NOTICE: row = {7,8,9} NOTICE: row = {10,11,12}

40.6.6. Trapping Errors

By default, any error occurring in a PL/pgSQL function aborts execution of the function, and indeed of the surrounding transaction as well. You can trap errors and recover from them by using a block with an clause. The syntax is an extension of the normal syntax for a block:

[ <<>> ] [ DECLARE ] BEGIN EXCEPTION WHEN [ OR ... ] THEN [ WHEN [ OR ... ] THEN ... ] END;

If no error occurs, this form of block simply executes all the , and then control passes to the next statement after . But if an error occurs within the , further processing of the is abandoned, and control passes to the list. The list is searched for the first matching the error that occurred. If a match is found, the corresponding are executed, and then control passes to the next statement after . If no match is found, the error propagates out as though the clause were not there at all: the error can be caught by an enclosing block with , or if there is none it aborts processing of the function.

The names can be any of those shown in Appendix A. A category name matches any error within its category. The special condition name matches every error type except . (It is possible, but often unwise, to trap by name.) Condition names are not case-sensitive. Also, an error condition can be specified by code; for example these are equivalent:

WHEN division_by_zero THEN ... WHEN SQLSTATE '22012' THEN ...

If a new error occurs within the selected , it cannot be caught by this clause, but is propagated out. A surrounding clause could catch it.

When an error is caught by an clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back. As an example, consider this fragment:

INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones'); BEGIN UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones'; x := x + 1; y := x / 0; EXCEPTION WHEN division_by_zero THEN RAISE NOTICE 'caught division_by_zero'; RETURN x; END;

When control reaches the assignment to , it will fail with a error. This will be caught by the clause. The value returned in the statement will be the incremented value of , but the effects of the command will have been rolled back. The command preceding the block is not rolled back, however, so the end result is that the database contains not .

Tip: A block containing an clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use without need.

Example 40-2. Exceptions with /

This example uses exception handling to perform either or , as appropriate:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN LOOP -- first try to update the key UPDATE db SET b = data WHERE a = key; IF found THEN RETURN; END IF; -- not there, so try to insert the key -- if someone else inserts the same key concurrently, -- we could get a unique-key failure BEGIN INSERT INTO db(a,b) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN -- Do nothing, and loop to try the UPDATE again. END; END LOOP; END; $$ LANGUAGE plpgsql; SELECT merge_db(1, 'david'); SELECT merge_db(1, 'dennis');

This coding assumes the error is caused by the , and not by, say, an in a trigger function on the table. It might also misbehave if there is more than one unique index on the table, since it will retry the operation regardless of which index caused the error. More safety could be had by using the features discussed next to check that the trapped error was the one expected.

40.6.6.1. Obtaining Information About an Error

Exception handlers frequently need to identify the specific error that occurred. There are two ways to get information about the current exception in PL/pgSQL: special variables and the command.

Within an exception handler, the special variable contains the error code that corresponds to the exception that was raised (refer to Table A-1 for a list of possible error codes). The special variable contains the error message associated with the exception. These variables are undefined outside exception handlers.

Within an exception handler, one may also retrieve information about the current exception by using the command, which has the form:

GET STACKED DIAGNOSTICS { = | := } [ , ... ];

Each is a key word identifying a status value to be assigned to the specified (which should be of the right data type to receive it). The currently available status items are shown in Table 40-2.

Table 40-2. Error Diagnostics Items

NameTypeDescription
the SQLSTATE error code of the exception
the name of the column related to exception
the name of the constraint related to exception
the name of the data type related to exception
the text of the exception's primary message
the name of the table related to exception
the name of the schema related to exception
the text of the exception's detail message, if any
the text of the exception's hint message, if any
line(s) of text describing the call stack at the time of the exception (see Section 40.6.7)

If the exception did not set a value for an item, an empty string will be returned.

Here is an example:

DECLARE text_var1 text; text_var2 text; text_var3 text; BEGIN -- some processing which might cause an exception ... EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT, text_var2 = PG_EXCEPTION_DETAIL, text_var3 = PG_EXCEPTION_HINT; END;

40.6.7. Obtaining Execution Location Information

The command, previously described in Section 40.5.5, retrieves information about current execution state (whereas the command discussed above reports information about the execution state as of a previous error). Its status item is useful for identifying the current execution location. returns a text string with line(s) of text describing the call stack. The first line refers to the current function and currently executing command. The second and any subsequent lines refer to calling functions further up the call stack. For example:

CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$ BEGIN RETURN inner_func(); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$ DECLARE stack text; BEGIN GET DIAGNOSTICS stack = PG_CONTEXT; RAISE NOTICE E'--- Call Stack ---\n%', stack; RETURN 1; END; $$ LANGUAGE plpgsql; SELECT outer_func(); NOTICE: --- Call Stack --- PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS PL/pgSQL function outer_func() line 3 at RETURN CONTEXT: PL/pgSQL function outer_func() line 3 at RETURN outer_func ------------ 1 (1 row)

returns the same sort of stack trace, but describing the location at which an error was detected, rather than the current location.

Sours: https://www.postgresql.org/docs/9.4/plpgsql-control-structures.html
How to Use Citus to Shard Postgres, for Performance \u0026 Scale - Data Exposed

Posted on by Hans-Jürgen Schönig

PL/pgSQL is the preferred way to write stored procedures in PostgreSQL. Of course there are more languages to write code available but most people still use PL/pgSQL to get the job done. However, debugging PL/pgSQL code can be a bit tricky. Tools are around but it is still not a fun experience.

One thing to make debugging easier is GET STACKED DIAGNOSTICS which is unfortunately not widely known. This post will show what it does and how you can make use of it.

Debugging PostgreSQL stored procedures

To show you how GET STACKED DIAGNOSTICS worked I have written some broken code which executes a division by zero which is forbidden in any sane database:

CREATE OR REPLACE FUNCTION broken_function() RETURNS void AS $$ BEGIN SELECT 1 / 0; END; $$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION simple_function() RETURNS numeric AS $$ DECLARE BEGIN RAISE NOTICE 'crazy function called ...'; PERFORM broken_function(); RETURN 0; END; $$ LANGUAGE 'plpgsql';

The question now is: How can we get a backtrace and debug the code? One way is to wrap the code into one more function call and see where things fail:

CREATE OR REPLACE FUNCTION get_stack() RETURNS void AS $$ DECLARE v_sqlstate text; v_message text; v_context text; BEGIN PERFORM simple_function(); EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS v_sqlstate = returned_sqlstate, v_message = message_text, v_context = pg_exception_context; RAISE NOTICE 'sqlstate: %', v_sqlstate; RAISE NOTICE 'message: %', v_message; RAISE NOTICE 'context: %', v_context; END; $$ LANGUAGE 'plpgsql';

My function catches the error causes by simple_function() and calls GET STACKED DIAGNOSTICS to display all the information we can possibly extract from the system. The output looks as follows:

test=# SELECT get_stack(); NOTICE: crazy function called ... NOTICE: sqlstate: 22012 NOTICE: message: division by zero NOTICE: context: SQL statement "SELECT 1 / 0" PL/pgSQL function broken_function() line 3 at SQL statement SQL statement "SELECT broken_function()" PL/pgSQL function simple_function() line 5 at PERFORM SQL statement "SELECT simple_function()" PL/pgSQL function get_stack() line 7 at PERFORM get_stack ----------- (1 row)

As you can see the stack trace is displayed and we can easily figure out where the problem has happened. In this case “broken_function” line 3 is the root cause of all evil.

Finally …

If you want to learn more about PostgreSQL consider checking out one of my older posts dealing with database performance.

Sours: https://www.cybertec-postgresql.com/en/debugging-pl-pgsql-get-stacked-diagnostics/

Now discussing:

You .you .you are crazy.



857 858 859 860 861