How Can I Use Variables and Retrieve the Last Inserted ID in PostgreSQL?

PostgreSQL script variable

In MS-SQL, you can use the DeCLARE keyword to declare and use variables in the query window. This allows you to store and operate data dynamically. For example, you can declare a variable called @list and assign a value, and then use it in the Select statement to retrieve the data based on the value of the variable.

Use variables in PostgreSQL

To implement similar functions in PostgreSQL, you can use the new anonymous code block function introduced in version 9.0. This feature allows you to execute code blocks in a SQL statement.

Example:

Get the last inserted ID

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

You can also use this code block function to retrieve the last inserted ID after the INSERT operation:

Please refer to the official PostgreSQL document to understand the complete discussion of using variables in the PostgreSQL script.

<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>

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