How to Store PostgreSQL Query Results in PL/pgSQL Variables?

Efficiently Storing PostgreSQL Query Results in PL/pgSQL Variables

PL/pgSQL, PostgreSQL's procedural language, provides a streamlined method for storing query results in variables. Let's examine a practical example:

<code class="language-sql">CREATE OR REPLACE FUNCTION test(x numeric)
RETURNS character varying AS
$BODY$
DECLARE
  name character varying(255);
BEGIN
  SELECT test_table.name INTO name FROM test_table WHERE id = x;
  RETURN name;
END;
$BODY$ LANGUAGE plpgsql;</code>

This function demonstrates how to store the result of a query into a variable. The core element is the SELECT ... INTO statement:

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

This statement retrieves the name field from the test_table where the id column matches the input parameter x, and assigns the retrieved value to the name variable. Note the use of test_table.name; this fully qualified column name is crucial to prevent ambiguity and potential errors. The function then returns the stored value.

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