database 使用 PL/pgSQL 将查询结果存储在变量中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12328198/
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
Store query result in a variable using in PL/pgSQL
提问by Sathish
How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL?
如何将查询结果分配给 PL/pgSQL（PostgreSQL 的过程语言）中的变量？
I have a function:
我有一个功能：
CREATE OR REPLACE FUNCTION test(x numeric)
RETURNS character varying AS
$BODY$
DECLARE
name character varying(255);
begin
name ='SELECT name FROM test_table where id='||x;
if(name='test')then
--do somthing
else
--do the else part
end if;
end;
return -- return my process result here
$BODY$
LANGUAGE plpgsql VOLATILE
In the above function I need to store the result of this query:
在上面的函数中，我需要存储这个查询的结果：
'SELECT name FROM test_table where id='||x;
to the variable name.
到变量name。
How to process this?
这个怎么处理？
回答by mu is too short
I think you're looking for SELECT INTO:
我认为您正在寻找SELECT INTO：
select test_table.name into name from test_table where id = x;
That will pull the namefrom test_tablewhere idis your function's argument and leave it in the namevariable. Don't leave out the table name prefix on test_table.nameor you'll get complaints about an ambiguous reference.
这name将从test_tablewhereid是你的函数的参数中提取并将其留在name变量中。不要遗漏表名前缀，test_table.name否则您会收到关于不明确引用的投诉。
回答by Erwin Brandstetter
As long as you are assigning a single variable, you can also use plain assignment in a plpgsql function:
只要您分配单个变量，您也可以在 plpgsql 函数中使用普通分配：
name := (SELECT t.name from test_table t where t.id = x);
Or use SELECT INTOlike @mu already provided.
或者SELECT INTO像@mu 已经提供的那样使用。
This works, too:
这也有效：
name := t.name from test_table t where t.id = x;
But better use one of the first two, clearer methods, as @Pavel commented.
但是最好使用前两种更清晰的方法之一，正如@Pavel 所评论的那样。
I shortened the syntax with a table alias additionally.
Update: I removed my code example and suggest to use IF EXISTS()instead like provided by @Pavel.
我还使用表别名缩短了语法。
更新：我删除了我的代码示例，并建议使用IF EXISTS()，而不是像由@Pavel提供。
回答by Pavel Stehule
The usual pattern is EXISTS(subselect):
通常的模式是EXISTS(subselect)：
BEGIN
IF EXISTS(SELECT name
FROM test_table t
WHERE t.id = x
AND t.name = 'test')
THEN
---
ELSE
---
END IF;
This pattern is used in PL/SQL, PL/pgSQL, SQL/PSM, ...
该模式用于 PL/SQL、PL/pgSQL、SQL/PSM、...
回答by Ram Pukar
Create Learning Table:
创建学习表：
CREATE TABLE "public"."learning" (
"api_id" int4 DEFAULT nextval('share_api_api_id_seq'::regclass) NOT NULL,
"title" varchar(255) COLLATE "default"
);
Insert Data Learning Table:
插入数据学习表：
INSERT INTO "public"."learning" VALUES ('1', 'Google AI-01');
INSERT INTO "public"."learning" VALUES ('2', 'Google AI-02');
INSERT INTO "public"."learning" VALUES ('3', 'Google AI-01');
Step: 01
步骤：01
CREATE OR REPLACE FUNCTION get_all (pattern VARCHAR) RETURNS TABLE (
learn_id INT,
learn_title VARCHAR
) AS $$
BEGIN
RETURN QUERY SELECT
api_id,
title
FROM
learning
WHERE
title = pattern ;
END ; $$ LANGUAGE 'plpgsql';
Step: 02
步骤：02
SELECT * FROM get_all('Google AI-01');
Step: 03
步骤：03
DROP FUNCTION get_all();
Demo:
演示：
回答by rinku Choudhary
You can use the following example to store a query result in a variable using PL/pgSQL:
您可以使用以下示例将查询结果存储在使用 PL/pgSQL 的变量中：
select * into demo from maintenanceactivitytrack ;
raise notice'p_maintenanceid:%',demo;
