blocks|key|1578780|text|下面是在plpgsql中使用变量的示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1578781|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|code-block|syntax|javascript|1578782|有关更多信息，请查看plpgsql+docs。|offset|length|1578783|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/8.3/static/plpgsql.html^0|0|0|A|C|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

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

<pre><code>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
</code></pre>

Have a look at the <a href="http://www.postgresql.org/docs/8.3/static/plpgsql.html" rel="noreferrer">plpgsql docs</a> for more information.

blocks|key|1363658|text|我遇到过一些其他的文档，他们使用\set来声明脚本变量，但是值看起来像常量值，我正在寻找一种方法，可以像变量一样工作，而不是常量变量。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1363659|例如：|1363660|\set+Comm+150

select+sal,+sal%2B:Comm+from+emp|code-block|syntax|javascript|1363661|这里，sal是表'emp‘中的值，comm是常量值。|1363662|entityMap^0|G|4|0|0|0|3|3|H|4|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|U|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|V|8|@$9|W|A|X|B|C]|$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

I've came across some other documents which they use <code>\set</code> to 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.

Ex:

<pre><code>\set Comm 150

select sal, sal+:Comm from emp
</code></pre>

Here <code>sal</code> is the value that is present in the table 'emp' and <code>comm</code> is the constant value.

blocks|key|1578895|text|完整答案位于official+PostgreSQL+documentation中。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1578896|您可以使用新的PG9.0匿名代码块功能(http://www.postgresql.org/docs/9.1/static/sql-do.html+)|1578897|DO+$$
DECLARE+v_List+TEXT;
BEGIN
++v_List+:=+'foobar'+;
++SELECT+*
++FROM+++dbo.PubLists
++WHERE++Name+=+v_List;
++--+...
END+$$;|code-block|syntax|javascript|1578898|您还可以获得最后一个insert+id：|1578899|DO+$$
DECLARE+lastid+bigint;
BEGIN
++INSERT+INTO+test+(name)+VALUES+('Test+Name')+
++RETURNING+id+INTO+lastid;

++SELECT+*+FROM+test+WHERE+id+=+lastid;
END+$$;|1578900|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/9.1/interactive/plpgsql-structure.html|1|http://www.postgresql.org/docs/9.1/static/sql-do.html|2|http://www.postgresql.org/docs/9.1/static/sql-insert.html^0|6|X|0|0|K|1H|1|0|0|A|6|2|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|F|3|G|5|H|7|18|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|19|8|@]|9|@$A|1A|B|1B|1|1C]]|C|$]]|$1|M|3|N|5|H|7|1D|8|@]|9|@]|C|$I|J]]|$1|O|3|-4|5|6|7|1E|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]|W|$5|R|S|T|C|$U|X]]|Y|$5|R|S|T|C|$U|Z]]]]

Complete answer is located in the <a href="http://www.postgresql.org/docs/9.1/interactive/plpgsql-structure.html">official PostgreSQL documentation</a>.

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

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

Also you can get the last <a href="http://www.postgresql.org/docs/9.1/static/sql-insert.html">insert</a> id:

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

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

blocks|key|1578918|text|您可以使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1578919|\set+list+'''foobar'''
SELECT+*+FROM+dbo.PubLists+WHERE+name+=+:list;|code-block|syntax|javascript|1578920|那个可以|1578921|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can use:

<pre><code>\set list '''foobar'''
SELECT * FROM dbo.PubLists WHERE name = :list;
</code></pre>

That will do

blocks|key|1578937|text|DO+$$
DECLARE++
+++a+integer+:=+10;++
+++b+integer+:=+20;++
+++c+integer;++
BEGIN++
+++c+:=+a+%2B+b;
++++RAISE+NOTICE'Value+of+c:+%25',+c;
END+$$;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1578938|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

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

blocks|key|1363760|text|我不得不做这样的事情|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1363761|CREATE+OR+REPLACE+FUNCTION+MYFUNC()
RETURNS+VOID+AS+$$
DO
$do$
BEGIN
DECLARE
+myvar+int;
+...
END
$do$
$$+LANGUAGE+SQL;|code-block|syntax|javascript|1363762|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I had to do something like this

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

blocks|key|1579007|text|Postgresql没有裸变量，你可以使用临时表。变量仅在代码块中可用，或作为用户界面功能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1579008|如果你需要一个空变量，你可以使用一个临时表：|1579009|CREATE+TEMP+TABLE+list+AS+VALUES+('foobar');

SELECT+dbo.PubLists.*
FROM+++dbo.PubLists,list
WHERE++Name+=+list.column1;|code-block|syntax|javascript|1579010|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

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

<pre><code>CREATE TEMP TABLE list AS VALUES ('foobar');

SELECT dbo.PubLists.*
FROM dbo.PubLists,list
WHERE Name = list.column1;
</code></pre>

blocks|key|1579042|text|在@nad2000的答案和@Pavel's+answer+here的基础上，这就是我的Flyway迁移脚本的终点。处理手动修改数据库架构的情况。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1579043|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+$$;|code-block|syntax|javascript|1579044|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/12333544/939860^0|D|K|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Building on @nad2000's answer and <a href="https://stackoverflow.com/a/12333544/939860">@Pavel's answer here</a>, this is where I ended up for my Flyway migration scripts. Handling for scenarios where the database schema was manually modified.

<pre><code>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 &gt; 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 $$;
</code></pre>

blocks|key|1579053|text|例如，对于使用alter+table中的变量：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1579054|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+'+%7C%7C+name_pk;|code-block|syntax|javascript|1579055|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For use variables in for example alter table:

<pre><code>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;
</code></pre>

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

<pre><code>DECLARE @List AS VARCHAR(8)

SELECT @List = 'foobar'

SELECT *
FROM dbo.PubLists
WHERE Name = @List
</code></pre>

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

How do you use variables in a simple PostgreSQL script?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

例如，在MS-SQL中，您可以打开一个查询窗口并运行以下命令：DECLARE @List AS VARCHAR(8)SELECT @List = 'foobar'SELECT *FROM   dbo.PubListsWHERE  Name = @List在PostgreSQL中如何做到这一点？这是可以做到的吗？

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

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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