在 PostgreSQL 中声明临时变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1941225/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Declaring temporary variables in PostgreSQL
提问by Earlz
I'm migrating from SQL Server to PostgreSQL. I've seen from How to declare a variable in a PostgreSQL querythat there is no such thing as temporary variables in native sql queries.
我正在从 SQL Server 迁移到 PostgreSQL。我从如何在 PostgreSQL 查询中声明变量中看到，在本机 sql 查询中没有临时变量这样的东西。
Well, I pretty badly need a few... How would I go about mixing in plpgsql? Must I create a function and then delete the function in order to get access to a language? that just seems error prone to me and I'm afraid I'm missing something.
好吧，我非常需要一些......我将如何混合plpgsql？我是否必须创建一个函数然后删除该函数才能访问一种语言？这对我来说似乎很容易出错，恐怕我错过了一些东西。
EDIT:
编辑：
cmd.CommandText="insert......" +
"declare @app int; declare @gid int;"+
"set @app=SCOPE_IDENTITY();"+ //select scope_identity will give us our RID that we just inserted
"select @gid=MAX(GROUPID) from HOUSEHOLD; set @gid=@gid+1; "+
"insert into HOUSEHOLD (APPLICANT_RID,GROUPID,ISHOH) values "+
"(@app,@gid,1);"+
"select @app";
rid=cmd.ExecuteScalar();
A direct rip from the application in which it's used. Note we are in the process of converting from SQL server to Postgre. (also, I've figured out the scope_identity()bit I think)
直接从使用它的应用程序中提取。请注意，我们正在从 SQL 服务器转换为 Postgre。（另外，我已经弄清楚了scope_identity()我的想法）
回答by Matthew Wood
What is your schema for the table being inserted? I'll try and answer based on this assumption of the schema:
您插入的表的架构是什么？我将尝试根据架构的这种假设来回答：
CREATE TABLE HOUSEHOLD (
APPLICANT_RID SERIAL, -- PostgreSQL auto-increment
GROUPID INTEGER,
ISHOH INTEGER
);
If I'm understanding your intent correctly, in PostgreSQL >= 8.2, the query would then be:
如果我正确理解您的意图，在 PostgreSQL >= 8.2 中，查询将是：
INSERT INTO HOUSEHOLD (GROUPID, ISHOH)
VALUES ((SELECT COALESCE(MAX(GROUPID)+1,1) FROM HOUSEHOLD), 1)
RETURNING APPLICANT_RID;
-- Added call to the COALESCE function to cover the case where HOUSEHOLD
-- is empty and MAX(GROUPID) returns NULL
In PostgreSQL >= 8.2, any INSERT/DELETE/UPDATE query may have a RETURNING clause that acts like a simple SELECT performed on the result set of the change query.
在 PostgreSQL >= 8.2 中，任何 INSERT/DELETE/UPDATE 查询都可能有一个 RETURNING 子句，它的作用类似于对更改查询的结果集执行的简单 SELECT。
回答by filiprem
Must I create a function and then delete the function in order to get access to a language?
我是否必须创建一个函数然后删除该函数才能访问一种语言？
Yes, but this shortcoming is going to be removed in PostgreSQL 8.5, with the addition of DO command. 8.5 is going to be released in 2010.
是的，但是这个缺点将在 PostgreSQL 8.5 中删除，添加 DO 命令。8.5 将在 2010 年发布。
回答by Tobu
If you're using a language binding, you can hold the variables there.
如果您使用语言绑定，则可以将变量保存在那里。
For example with SQLAlchemy (python):
例如使用 SQLAlchemy (python)：
my_var = 'Reynardine'
session.query(User.name).filter(User.fullname==my_var)
If you're in psql, you have variables:
如果你在 psql 中，你有变量：
\set a 5
SELECT :a;
And if your logic is in PL/pgSQL:
如果您的逻辑在 PL/pgSQL 中：
tax := subtotal * 0.06;
回答by Arthur Thomas
you install a language that you want to use with the CREATE LANGUAGE command for known languages. Although you can use other languages.
您安装了一种语言，该语言要与已知语言的 CREATE LANGUAGE 命令一起使用。虽然你可以使用其他语言。
You will have to create a function to use it. If you do not want to make a permanent function in the db then the other choice would be to use a scrip in python or something that uses a postgresql driver to connect to the db and do queries. You can then manipulate or look through the data in the script. For instance in python you would install the pygresql library and in your script import pgdb which you can use to connect to the db.
您必须创建一个函数才能使用它。如果您不想在 db 中创建永久函数，那么另一种选择是在 python 中使用脚本或使用 postgresql 驱动程序连接到 db 并执行查询的内容。然后，您可以操作或查看脚本中的数据。例如在 python 中，您将安装 pygresql 库并在您的脚本中导入 pgdb，您可以使用它来连接到数据库。
回答by Alex Brasetvik
You can also declare session variables using plperl - http://www.postgresql.org/docs/8.4/static/plperl-global.html
您还可以使用 plperl 声明会话变量 - http://www.postgresql.org/docs/8.4/static/plperl-global.html
回答by Andrew Hare
I think that PostgreSQL's row-type variablewould be the closest thing:
我认为 PostgreSQL 的行类型变量将是最接近的：
A variable of a composite type is called a row variable (or row-type variable). Such a variable can hold a whole row of a SELECT or FOR query result, so long as that query's column set matches the declared type of the variable.
复合类型的变量称为行变量（或行类型变量）。这样的变量可以保存一整行 SELECT 或 FOR 查询结果，只要该查询的列集与变量的声明类型相匹配。
回答by leroyJr
You mentioned the post (How to declare a variable in a PostgreSQL query).
您提到了这篇文章（如何在 PostgreSQL 查询中声明变量）。
I believe there is a suitable answer farther down the chain of solutions if using psql and the \set command:
如果使用 psql 和 \set 命令，我相信在解决方案链的更远的地方有一个合适的答案：
my_db=> \set myvar 5
my_db=> SELECT :myvar + 1 AS my_var_plus_1;
