如何在简单的PostgreSQL脚本中使用变量? [英] How do you use variables in a simple PostgreSQL script?

问题描述

例如，在MS-SQL中，您可以打开查询窗口并运行以下命令:

For example, in MS-SQL, you can open up a query window and run the following:

DECLARE @List AS VARCHAR(8)

SELECT @List = 'foobar'

SELECT *
FROM   dbo.PubLists
WHERE  Name = @List

这在PostgreSQL中如何完成?能做到吗?

How is this done in PostgreSQL? Can it be done?

推荐答案

完整答案位于 PostgreSQL官方文档.

您可以使用新的PG9.0匿名代码块功能( http://www.postgresql .org/docs/9.1/static/sql-do.html )

You can use new PG9.0 anonymous code block feature (http://www.postgresql.org/docs/9.1/static/sql-do.html )

DO $$
DECLARE v_List TEXT;
BEGIN
  v_List := 'foobar' ;
  SELECT *
  FROM   dbo.PubLists
  WHERE  Name = v_List;
  -- ...
END $$;

您还可以获取最后一个插入 ID:

Also you can get the last insert id:

DO $$
DECLARE lastid bigint;
BEGIN
  INSERT INTO test (name) VALUES ('Test Name') 
  RETURNING id INTO lastid;

  SELECT * FROM test WHERE id = lastid;
END $$;

这篇关于如何在简单的PostgreSQL脚本中使用变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！