40

I need to assign values to 2 variable as below in Postgres function.

a := select col1 from tbl where ...
b := select col2 from tbl where ...

How can I assign 2 values to 2 variables in one line command?

Like

a,b := select col1,col2 from tbl where ...
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Xianlin
  • 567
  • 2
  • 6
  • 10

1 Answers1

61

As said in "40.5.3. Executing a Query with a Single-row Result" (emphasis mine):

The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding an INTO clause.

So this should work:

SELECT col1, col2 INTO a, b FROM tbl WHERE...;
Milen A. Radev
  • 1,608
  • 14
  • 13