How Can I Store PostgreSQL Query Results in a PL/pgSQL Variable?

Use PL/pgSQL to store query results into variables

PostgreSQL’s procedural language PL/pgSQL allows assigning the results of SQL queries to variables. This enables efficient processing and manipulation of data in stored procedures and functions.

SELECT INTO syntax

To store query results into variables, PL/pgSQL uses the SELECT INTO syntax. The syntax has the form:

<code class="language-sql">SELECT select_expressions INTO target_variable FROM table_or_query_expression;</code>

Example:

The following PL/pgSQL function attempts to retrieve a name from a table based on an input ID:

<code class="language-sql">-- PL/pgSQL 函数
CREATE OR REPLACE FUNCTION test(x numeric)
RETURNS character varying AS
$BODY$
DECLARE
  name character varying(255);
BEGIN
  -- 正确的方法
  SELECT name INTO name FROM test_table WHERE id = x;
  RETURN name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;</code>

In this example, we use SELECT INTO to assign the value of the test_table field in the name table to the variable name. This ensures that the variable contains the desired result.

The

SELECT clause specifies the test_table columns selected from the name table. INTO The keyword is followed by the variable (name) in which the result will be stored. The EXCEPTION block is added to the error handling section to handle the NO_DATA_FOUND situation and return NULL.

The above is the detailed content of How Can I Store PostgreSQL Query Results in a PL/pgSQL Variable?. For more information, please follow other related articles on the PHP Chinese website!