CODEWITHSUNDEEP
postgresqlvariable-assignmentplpgsqldynamic-sql
Sarfaraz Mak
Sarfaraz Mak

Reputation: 141

How to assign selected values from a table to specific variables in Pl/pgSQL?

For example, here is what I am trying to accomplish:

EXECUTE 'SELECT name, address INTO variable1, variable2
         FROM employee WHERE id=1';

Upvotes: 1

Views: 2084

Answers (3)

Peter Krauss
Peter Krauss

Reputation: 13982

Assign value, general note

(see this other question for assign value to variable at declaration section)

The language PLpgSQL syntax have many ways to say:

 Y := f(X);

The EXECUTE clause is only for "dynamic execution" (less performance),

 EXECUTE 'f(X)' INTO Y;

Use Y := f(X); or SELECT for execute static declarations,

 SELECT f(X) INTO Y;

Use PERFORM statment when discard the results or to work with void returns:

 PERFORM f(X);

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658757

Better alternative

Actually, there is nothing indicating a need for dynamic SQL. All identifiers are stable. Assuming you only want to parameterize the id value, simplify to:

SELECT name,address from employee where id = $1
INTO   variable1, variable2

Or:

SELECT INTO variable1, variable2
            e.name,    e.address
FROM   employee e
WHERE  e.id = _my_variable;

The appropriate chapter in the manual: "Executing a Query with a Single-row Result"

Dynamic SQL would be indicated when working with parameterized table or column names.
Here is a list of related question for dynamic SQL

Cause for immediate problem

The reason why your original code did not work, per documentation:

SELECT INTO is not currently supported within EXECUTE; instead, execute a plain SELECT command and specify INTO as part of the EXECUTE itself.

@a_horse demonstrates valid syntax in his answer.

Either alternative relies on the query to return a single row. If your id is defined unique, that cannot break.

Upvotes: 1

user330315
user330315

Reputation: 

EXECUTE 'SELECT name,address from employee where id=1'
   INTO variable1, variable2;

More details in the manual: http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Better to also use a parameter instead of concatenated values (to prevent SQL injection):

l_emp_id := 1;
EXECUTE 'SELECT name,address from employee where id=$1'
   INTO variable1, variable2
   USING l_emp_id;

Upvotes: 1

Related Questions