23.5. Basic Statements

23.5.1. Assignment

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

identifier := expression;

As explained above, the expression in such a statement is evaluated by means of an SQL SELECT 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 (as for char(20)), 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 runtime 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;

23.5.2. SELECT INTO

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

SELECT INTO target expressions FROM ...;

where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields. Note that this is quite different from PostgreSQL's normal interpretation of SELECT INTO, which is that the INTO target is a newly created table. (If you want to create a table from a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE ... AS SELECT.)

If a row or a variable list is used as target, the selected values must exactly match the structure of the target(s), or a runtime error occurs. When a record variable is the target, it automatically configures itself to the rowtype of the query result columns.

Except for the INTO clause, the SELECT statement is the same as a normal SQL SELECT query and can use the full power of SELECT.

If the SELECT query returns zero rows, NULLs are assigned to the target(s). If the SELECT 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 ORDER BY.)

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

There is a special variable named FOUND of type boolean that can be used immediately after a SELECT INTO to check if an assignment had success (that is, at least one row was returned by the SELECT). For example,

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

Alternatively, you can use the IS NULL (or ISNULL) conditional to test for NULLity of a RECORD/ROW result. Note that there is no way to tell whether any additional rows might have been discarded.

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;

23.5.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 PERFORM statement:

PERFORM query;

This executes a SELECT query and discards the result. PL/pgSQL variables are substituted into the query as usual.

An example:

PERFORM create_mv(''cs_session_page_requests_mv'',''
     SELECT   session_id, page_id, count(*) AS n_hits,
              sum(dwell_time) AS dwell_time, count(dwell_time) AS dwell_count
     FROM     cs_fact_table
     GROUP BY session_id, page_id '');

23.5.4. Executing dynamic queries

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

EXECUTE query-string;

where query-string is an expression yielding a string (of type text) containing the query 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 query string. The values of variables must be inserted into the query string as it is constructed.

When working with dynamic queries you will have to face escaping of single quotes in PL/pgSQL. Please refer to the table in Section 23.11 for a detailed explanation that will save you some effort.

Unlike all other queries in PL/pgSQL, a query run by an EXECUTE statement is not prepared and saved just once during the life of the server. Instead, the query is prepared each time the statement is run. The query-string can be dynamically created within the procedure to perform actions on variable tables and fields.

The results from SELECT queries are discarded by EXECUTE, and SELECT INTO is not currently supported within EXECUTE. So, the only way to extract a result from a dynamically-created SELECT is to use the FOR-IN-EXECUTE form described later.

An example:

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

This example shows use of the functions quote_ident(TEXT) and quote_literal(TEXT). Variables containing field and table identifiers should be passed to function quote_ident(). Variables containing literal elements of the dynamic query string should be passed to quote_literal(). Both take the appropriate steps to return the input text enclosed in single or double quotes and with any embedded special characters properly escaped.

Here is a much larger example of a dynamic query and EXECUTE:

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'''';''; 
 
    -- This works because we are not substituting any variables
    -- Otherwise it would fail. Look at PERFORM for another way to run functions
    
    EXECUTE a_output; 
END; 
' LANGUAGE 'plpgsql';

23.5.5. Obtaining result status

GET DIAGNOSTICS variable = item [ , ... ] ;

This command allows retrieval of system status indicators. Each item is a keyword 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 ROW_COUNT, the number of rows processed by the last SQL query sent down to the SQL engine; and RESULT_OID, the Oid of the last row inserted by the most recent SQL query. Note that RESULT_OID is only useful after an INSERT query.