patternsqlCritical
Postgres function assign query results to multiple variables
Viewed 0 times
postgresqueryfunctionmultiplevariablesresultsassign
Problem
I need to assign values to 2 variable as below in Postgres function.
How can I assign 2 values to 2 variables in one line command?
Like
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 ...Solution
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:
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...;Code Snippets
SELECT col1, col2 INTO a, b FROM tbl WHERE...;Context
StackExchange Database Administrators Q#54011, answer score: 61
Revisions (0)
No revisions yet.