How Can I Use Variables in PostgreSQL Queries?

Working with Variables in PostgreSQL

Unlike MS-SQL's DECLARE statement, PostgreSQL uses anonymous code blocks (introduced in version 9.0) to manage variables within queries. This approach allows for variable declaration and manipulation within a structured block of code.

Practical Examples

Here's how you can utilize variables in PostgreSQL:

Example 1: Assigning and Using a Variable

<code class="language-sql">DO $$
DECLARE my_variable TEXT;
BEGIN
  my_variable := 'foobar';
  SELECT * FROM dbo.PubLists WHERE Name = my_variable;
END $$;</code>

This code snippet declares a text variable my_variable, assigns it the value 'foobar', and then uses it in a SELECT statement to filter data from the dbo.PubLists table.

Example 2: Retrieving the Last Inserted ID

<code class="language-sql">DO $$
DECLARE last_id bigint;
BEGIN
  INSERT INTO test (name) VALUES ('Test Name') RETURNING id INTO last_id;
  SELECT * FROM test WHERE id = last_id;
END $$;</code>

This example demonstrates how to capture the id of the last inserted row using the RETURNING clause and store it in the last_id variable. The subsequent SELECT statement then uses this variable to retrieve the newly inserted record.

For more detailed information and advanced techniques on using variables in PostgreSQL, refer to the official PostgreSQL documentation.

The above is the detailed content of How Can I Use Variables in PostgreSQL Queries?. For more information, please follow other related articles on the PHP Chinese website!