How Can I Declare and Use Variables in PostgreSQL Scripts?

Declaring and using variables in PostgreSQL scripts

Introduction

Variables play a vital role in SQL scripts, allowing us to store and manipulate data dynamically. Although variables are widely used in various database systems (such as MS-SQL), the way they are used may vary from platform to platform. This article explores how to use variables in PostgreSQL, clarifying valid declarations and tips for using them.

Answer: Declare and use variables in PostgreSQL

PostgreSQL provides several ways to declare and use variables in scripts. Here is a detailed explanation of each method:

Use anonymous code blocks (PostgreSQL 9.0 and later)

Anonymous code blocks introduced in PostgreSQL version 9.0 provide a straightforward way to declare and use variables. This approach consists of enclosing the variable declaration and SQL statement in a DO$$...$$ block:

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

Use DECLARE to declare variables

Prior to PostgreSQL 9.0, variables could be declared using the DECLARE statement. This statement initializes a variable with a specific data type and an optional initial value:

<code class="language-sql">DECLARE v_List VARCHAR(8) := 'foobar';

SELECT *
FROM   dbo.PubLists
WHERE  Name = v_List;</code>

Using variables in SQL statements

After declaration, variables can be used in SQL statements by adding a colon (:) before the variable. PostgreSQL supports parameter binding, allowing variables to be passed into SQL queries and other statements:

<code class="language-sql">SELECT *
FROM   dbo.PubLists
WHERE  Name = :v_List;</code>

Other functions

Get the last inserted ID:

Similar to MS-SQL, PostgreSQL provides a way to retrieve the last insertion ID using an anonymous code block:

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

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

Full document:

For more information, see the official PostgreSQL documentation on variables: https://www.php.cn/link/5898493fbdf4a44a24084021b2215f85

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