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

Using PL/pgSQL to Store PostgreSQL Query Results in Variables

PostgreSQL's PL/pgSQL procedural language offers several ways to retrieve data. A common method involves storing query results directly into variables. This is achieved using the SELECT INTO statement.

Let's examine this technique with an example:

<code class="language-sql">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;

    IF name = 'test' THEN
        -- Perform specific actions
    ELSE
        -- Execute alternative actions
    END IF;

    -- Return the function's result
    RETURN name; --Example return
END;
$BODY$
LANGUAGE plpgsql VOLATILE;</code>

In this function, SELECT name FROM test_table WHERE id = x fetches the name column from test_table where the id matches the input parameter x. The INTO name clause assigns the query's result to the name variable. Note that this is more concise than a separate assignment statement. The IF statement then conditionally executes different code blocks based on the value of name. Finally, a value is returned (in this case, name).

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