使用PL/pgSQL将查询结果存储在变量中

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.

变量名。

How to process this?

如何处理这个问题?

4 个解决方案

#1

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

这将从test_table中提取名称，其中id是函数的参数，并将其保留在name变量中。不要在test_table.name中省略表名前缀，否则您会收到关于不明确引用的抱怨。

#2

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 INTO like @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 exist()，如@Pavel所提供的那样。

#3

The usual pattern is EXISTS(subselect):

通常的模式是存在的(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、……

#4

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

步骤:1

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:

演示:

#1