How Can I Use Variables in PostgreSQL Like I Do in Microsoft SQL Server?

PostgreSQL Variable Usage: A Practical Guide

Microsoft SQL Server's convenient variable handling within query windows is mirrored in PostgreSQL, albeit with a slightly different approach. This guide demonstrates how to declare, assign, and utilize variables within PostgreSQL scripts.

Declaring and Using Variables in PostgreSQL

PostgreSQL employs the DO $$ anonymous code block for variable declaration and manipulation. This allows you to define variables with specific data types and assign them values. The following example illustrates this:

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

Retrieving the Last Inserted ID

A common task is retrieving the ID of the last inserted row. PostgreSQL efficiently handles this using the RETURNING clause within the INSERT statement, as shown below:

<code class="language-sql">DO $$
DECLARE last_inserted_id BIGINT;
BEGIN
  INSERT INTO test (name) VALUES ('Test Name')
  RETURNING id INTO last_inserted_id;

  SELECT * FROM test WHERE id = last_inserted_id;
END $$;</code>

This concise method demonstrates the power and flexibility of variable usage within PostgreSQL, offering functionality comparable to Microsoft SQL Server's variable handling.

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