如何在简单的 PostgreSQL 脚本中使用变量？

How do you use variables in a simple PostgreSQL script?

提问by nad2000

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

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

DECLARE @List AS VARCHAR(8)

SELECT @List = 'foobar'

SELECT *
FROM   dbo.PubLists
WHERE  Name = @List

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

这是如何在 PostgreSQL 中完成的？可以做到吗？

回答by nad2000

Complete answer is located in the official PostgreSQL documentation.

完整的答案位于官方 PostgreSQL 文档中。

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

您可以使用新的 PG9.0 匿名代码块功能（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 $$;

Also you can get the last insertid:

您还可以获得最后一个插入ID：

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

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

回答by Achilles Ram Nakirekanti

DO $$
DECLARE  
   a integer := 10;  
   b integer := 20;  
   c integer;  
BEGIN  
   c := a + b;
    RAISE NOTICE'Value of c: %', c;
END $$;

回答by Karim de Alba

You can use:

您可以使用：

\set list '''foobar'''
SELECT * FROM dbo.PubLists WHERE name = :list;

That will do

那会做

回答by overthink

Here's an example of using a variable in plpgsql:

这是在 plpgsql 中使用变量的示例：

create table test (id int);
insert into test values (1);
insert into test values (2);
insert into test values (3);

create function test_fn() returns int as $$
    declare val int := 2;
    begin
        return (SELECT id FROM test WHERE id = val);
    end;
$$ LANGUAGE plpgsql;

SELECT * FROM test_fn();
 test_fn 
---------
       2

Have a look at the plpgsql docsfor more information.

查看plpgsql 文档以获取更多信息。

回答by Vinodraj

I've came across some other documents which they use \setto declare scripting variable but the value is seems to be like constant value and I'm finding for way that can be acts like a variable not a constant variable.

我遇到了一些其他文档，它们\set用来声明脚本变量，但该值似乎类似于常量值，我正在寻找可以充当变量而不是常量变量的方法。

Ex:

前任：

\set Comm 150

select sal, sal+:Comm from emp

Here salis the value that is present in the table 'emp' and commis the constant value.

这sal是表 'emp' 中comm存在的值并且是常量值。

回答by Nick

I had to do something like this

我不得不做这样的事情

CREATE OR REPLACE FUNCTION MYFUNC()
RETURNS VOID AS $$
DO
$do$
BEGIN
DECLARE
 myvar int;
 ...
END
$do$
$$ LANGUAGE SQL;

回答by Jasen

Postgresql does not have bare variables, you could use a temporary table. variables are only available in code blocks or as a user-interface feature.

Postgresql 没有裸变量，您可以使用临时表。变量仅在代码块中或作为用户界面功能可用。

If you need a bare variable you could use a temporary table:

如果您需要一个裸变量，您可以使用临时表：

CREATE TEMP TABLE list AS VALUES ('foobar');

SELECT dbo.PubLists.*
FROM   dbo.PubLists,list
WHERE  Name = list.column1;

回答by Shane

Building on @nad2000's answer and @Pavel's answer here, this is where I ended up for my Flyway migration scripts. Handling for scenarios where the database schema was manually modified.

对@ nad2000答案，并建立@帕维尔的答案在这里，这是我结束了我的迁飞迁移脚本。处理手动修改数据库架构的场景。

DO $$
BEGIN
    IF NOT EXISTS(
        SELECT TRUE FROM pg_attribute 
        WHERE attrelid = (
            SELECT c.oid
            FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE 
                n.nspname = CURRENT_SCHEMA() 
                AND c.relname = 'device_ip_lookups'
            )
        AND attname = 'active_date'
        AND NOT attisdropped
        AND attnum > 0
        )
    THEN
        RAISE NOTICE 'ADDING COLUMN';        
        ALTER TABLE device_ip_lookups
            ADD COLUMN active_date TIMESTAMP;
    ELSE
        RAISE NOTICE 'SKIPPING, COLUMN ALREADY EXISTS';
    END IF;
END $$;

回答by David Leon

For use variables in for example alter table:

对于在例如alter table 中使用变量：

DO $$ 
DECLARE name_pk VARCHAR(200);
BEGIN
select constraint_name
from information_schema.table_constraints
where table_schema = 'schema_name'
      and table_name = 'table_name'
      and constraint_type = 'PRIMARY KEY' INTO name_pk;
IF (name_pk := '') THEN
EXECUTE 'ALTER TABLE schema_name.table_name DROP CONSTRAINT ' || name_pk;

回答by Milen A. Radev

For the official CLI client "psql" see here. And "pgAdmin3" 1.10 (still in beta) has "pgScript".

对于官方 CLI 客户端“psql”，请参见此处。而“pgAdmin3”1.10（仍处于测试阶段）有“ pgScript”。