How to Assign a PostgreSQL Query Result to a Variable in PL/pgSQL?

Storing PostgreSQL Query Results in PL/pgSQL Variables

Efficiently managing query results within PL/pgSQL functions is crucial for PostgreSQL database programming. This involves correctly assigning the output of a query to a variable for further processing.

Let's illustrate with a function that verifies the presence of a record based on its ID:

Incorrect Approach:

The following attempt to assign a query directly to a variable is flawed:

<code class="language-sql">name = 'SELECT name FROM test_table where id = x';</code>

This simply assigns the string of the SQL query to the variable, not the actual query result.

Correct Approach using SELECT INTO:

The proper method uses the SELECT INTO statement:

<code class="language-sql">SELECT test_table.name INTO name FROM test_table WHERE id = x;</code>

This executes the query and stores the retrieved name value into the name variable. Note the use of test_table.name to explicitly specify the column, preventing ambiguity. If the query returns multiple rows, only the first row's value will be assigned. If no rows are found, name will be set to NULL. For handling multiple rows, consider using a cursor or other appropriate techniques.

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