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

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

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?

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

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

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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