使用postgresql存储过程将查询结果存储在变量中 [英] Store the query result in variable using postgresql Stored procedure

问题描述

如何使用postgresql存储查询结果变量存储过程

How to store the query result in variable using postgresql Stored procedure

我有一个存储过程

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 eles part
 end if;
end;
return -- return my process result here
$BODY$
LANGUAGE plpgsql VOLATILE

在上面的过程中我需要存储

in the above procedure i need to store

'SELECT name FROM test_table where id='||id;

此查询将结果返回到变量名称

this query returning result to the variable name

如何处理这个？请让我知道

how to process this? please let me know

推荐答案

我想你正在寻找SELECT INTO ：

I think you're looking for SELECT INTO:

select test_table.name into name from test_table where id = x;

这将从 c $ c> test_table 其中 id 是您的函数的参数，并保留在 name 变量。不要在 test_table.name 上省略表名称前缀，否则将收到有关引用含糊不清的投诉。

That will pull the name from test_table where id is your function's argument and leave it in the name variable. Don't leave out the table name prefix on test_table.name or you'll get complaints about an ambiguous reference.

这篇关于使用postgresql存储过程将查询结果存储在变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！