PL/pgSQL basic statements

Introduction

In this section, we have described 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.

Version: 9.3

Assignment

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

variable:= expression;

Here is an example:

Examples:

tax:= subtotal * 0.04;
my_record.user_id := 30;

Executing a command with no result

There are some SQL commands that does not return rows, for example, INSERT without a RETURNING clause, you can execute the command within a PL/pgSQL function. The variable name appearing in the command text is treated as a parameter and the current value of the variable is treated as the parameter value at run time. Sometimes it is useful to evaluate an expression or SELECT 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 PERFORM statement:

Here is the general syntax of a variable declaration:

Syntax:

PERFORM query;

Examples:

Code:

CREATE OR REPLACE FUNCTION test() RETURNS void AS $$ 
INSERT INTO mytable VALUES (30),(50) 
$$ LANGUAGE sql;

Code:

CREATE OR REPLACE FUNCTION demo ()
RETURNS text AS $$
BEGIN
PERFORM test();
RETURN ’OK’;
END;
$$ LANGUAGE plpgsql;

Also you can write this in the following format

At first, create the function as follows

Code:

CREATE OR REPLACE FUNCTION test() RETURNS void AS $$ 
INSERT INTO mytable VALUES (30),(50) 
$$ LANGUAGE sql;

then the following

Code:

CREATE OR REPLACE FUNCTION demo ()
RETURNS text AS $$
BEGIN
PERFORM test();
RETURN ’OK’;
END;
$$ LANGUAGE plpgsql;

Here is the another format

Code:

CREATE OR REPLACE FUNCTION demo ()
RETURNS text AS $$
BEGIN
PERFORM (SELECT * FROM mytable);
RETURN ’OK’;
END;
$$ LANGUAGE plpgsql;

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 INTO clause. For example,

Syntax:

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

where a target 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 SELECT, INSERT/UPDATE/DELETE with RETURNING, and utility commands that return row-set results (such as EXPLAIN). Except for the INTO clause, the SQL command is the same as it would be written outside PL/pgSQL.

If a row or a variable list is used as a 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 a target, it automatically configures itself to the row type of the query result columns.

The INTO clause can appear almost anywhere in the SQL command. Customarily it is written either just before or just after the list of select_expressions in a SELECT 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 STRICT is not specified in the INTO clause, then the target 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 ORDER BY.) Any result rows after the first row are discarded. You can check the special FOUND variable to determine whether a row was returned:

Code:

CREATE FUNCTION test1(fstname text) RETURNS text AS $$
DECLARE
    fst_name text;
found_employee employees%ROWTYPE;
BEGIN
    SELECT * INTO found_employee FROM employees WHERE first_name = fst_name;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', fst_name;
END IF;

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

Code:

CREATE FUNCTION test3(fstname text) RETURNS text AS $$
DECLARE
    fst_name text;
found_employee employees%ROWTYPE;
BEGIN
    SELECT * INTO STRICT found_employee FROM employees WHERE first_name = fst_name;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', fst_name;
END IF;

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 40.10.2) will not work in such scenarios. To handle this sort of problem, the EXECUTE statement is provided:

Syntax:

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

where command-string is an expression yielding a string (of type text) containing the command to be executed. The optional target 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 USING expressions supply values to be inserted into the command.

The command string can use parameter values, which are referenced in the command as $1, $2, etc. These symbols refer to values supplied in the USING clause. This method is often preferable to inserting data values into the command string as text: it avoids the 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:

Examples :

Code:

CREATE FUNCTION test() RETURNS text AS $$
DECLARE
found_employee employees%ROWTYPE;
BEGIN
    SELECT * INTO STRICT found_employee FROM employees WHERE first_name = fst_name;
EXECUTE 'SELECT count(*) FROM employees WHERE manager_id<>0' into found_employee;
END;
$$ LANGUAGE plpgsql;

Next:

• Control Structures

Previous: Introduction to plpgSQL
Next: Control Structures