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

Question

如何在PostgreSQL的过程语言PL/pgSQL中将查询结果赋值给变量？

我有一个函数：

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

在上面的函数中，我需要存储这个查询的结果：

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

将其处理到变量name中。

如何处理？

- Sathish

Answer 1

我认为你正在寻找SELECT select_expressions INTO：

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

这将从test_table中获取name，其中id是您函数参数的值，并将其留在name变量中。不要忘记在test_table.name上加上表名前缀，否则您将会收到有关引用模糊的投诉。

- mu is too short

---

如果我需要多个变量，例如选择test_table.name、test_table.id和test_table.ssn怎么办？ - Dao Lam

从我所链接的文档中可以看到：“执行SQL命令得到的单行结果集（可能包含多列）可以赋值给记录变量、行类型变量或标量变量列表。” - mu is too short

@muistooshort，你的意思是说我可以做同样的事情，并且可以使用name.id、name.ssn来检索吗？我尝试过使用IF EXISTS，但没有成功：IF EXISTS（select * into name from test_table...）） - Dao Lam

文档中没有例子（或者我错过了），但是正如@muistooshort所指出的那样，您可以使用单个SELECT选择多个变量：SELECT test_table.column1, test_table.column2 INTO variable1, variable2 FROM test_table WHERE id = x; - Grengas

PostgreSQL文档将这种语法称为 SELECT select_expressions INTO（不会创建表），而不是 SELECT INTO（会创建表）。更多信息 - Edward Brey

Answer 2

要为一个单一变量赋值，你也可以在 PL/pgSQL 代码块中使用 普通赋值，并将 标量子查询 放在右边：

name := (SELECT t.name from test_table t where t.id = x);

与@mu提供的类似，实际上与SELECT INTO相同，但存在微妙的差异：

• 在我的Postgres 14测试中，SELECT INTO略快。
  （仍然不涉及SELECT的常量平面赋值速度比它快10倍。）
• SELECT INTO还设置了特殊变量FOUND，而简单的赋值没有。您可能想要其中之一。
• SELECT INTO也可以同时分配多个变量。请参见：
  • 使用多个属性进行SELECT INTO

值得注意的是，这也可以工作：

name := t.name from test_table t where t.id = x;

没有前导 SELECT 的 SELECT 语句。但我不会使用这种混合方式。最好使用前两种更清晰、有文档记录的方法，正如 @Pavel 所评论的那样。

- Erwin Brandstetter

---

这不是一个好主意 - 这个功能没有文档，而且很丑陋。 - Pavel Stehule

PL/pgSQL允许混合使用SQL和PL，有时候你可以创建出非常奇怪的东西，但最好还是在隔离的语句中清晰地混合PL和SQL。 - Pavel Stehule

@PavelStehule：我同意，你的表单更可取。 - Erwin Brandstetter

实际上，我更喜欢你的语法，但问题是当你想处理错误时，你的语句不会像select into语句一样将FOUND发送到true。请查看（https://www.postgresql.org/docs/9.1/plpgsql-statements.html）。 - SENHAJI RHAZI Hamza

Answer 3

通常的模式是EXISTS(subselect)：

BEGIN
  IF EXISTS(SELECT name
              FROM test_table t
             WHERE t.id = x
               AND t.name = 'test')
  THEN
     ---
  ELSE
     ---
  END IF;

这个模式在PL/SQL、PL/pgSQL、SQL/PSM等技术中使用...

- Pavel Stehule

Answer 4

创建学习桌：

CREATE TABLE "public"."learning" (
    "api_id" int4 DEFAULT nextval('share_api_api_id_seq'::regclass) NOT NULL,
    "title" varchar(255) COLLATE "default"
);

插入数据学习表：

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');

步骤：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';

步骤：02

SELECT * FROM get_all('Google AI-01');

步骤：03

DROP FUNCTION get_all();

演示：

- Ram Pukar

Answer 5

如果要执行只返回单行结果的查询，请使用以下语法：执行仅返回单行结果的查询。

SELECT select_expressions INTO [STRICT] target FROM ...

其中target可以是记录变量、行变量或由简单变量和记录/行字段组成的逗号分隔列表。

与SELECT INTO不同，SELECT select_expressions INTO不创建表。

在您的示例中，您只有一个简单变量name，因此选择语句将是：

SELECT test_table.name INTO name FROM test_table WHERE test_table.id = x;

- Edward Brey

Answer 6

这里的许多答案都省略了使用函数的重要部分，考虑到它的流行度，我认为很多人来到这里是想快速了解如何使用函数。

以下是在postgres中使用函数的示例（包括声明、变量、参数、返回值和运行）。下面是一种过度设计的方法，用于将右下角的“简介”推文更新为“hello world”。

id (serial)	pub_id (text)	tweet (text)
1	abc	hello world
2	def	blurb

-- Optional drop if replace fails below.
drop function if exists sync_tweets(text, text);

create or replace function sync_tweets(
    src_pub_id text, -- function arguments
    dst_pub_id text
) returns setof tweets as -- i.e. rows. int, text work too
$$
declare
    src_id    int; -- temp function variables (not args)
    dest_id   int;
    src_tweet text;
begin
    -- query result into a temp variable
    src_id := (select id from tweets where pub_id = src_pub_id);

    -- query result into a temp variable (another way)
    select tweet into src_tweet from tweets where id = src_id;

    dest_id := (select id from tweets where pub_id = dst_pub_id);
    update tweets set tweet=src_tweet where id = dest_id;

    return query -- i.e. rows, return 0 with return int above works too
        select * from tweets where pub_id in (src_pub_id, dst_pub_id);
end
$$ language plpgsql; -- need the language to avoid ERROR 42P13

-- Run it!
select * from sync_tweets('abc', 'def');

/*
  Outputs
   __________________________________________________ 
  |  id (serial)  |  pub_id (text)  |  tweet (text)  |
  |---------------|-----------------|----------------|
  |  1            |  abc            |  hello world   |
  |  2            |  def            |  blurb         |
  --------------------------------------------------
*/

- Josh Hibschman

Answer 7

您可以使用以下示例来使用PL/pgSQL将查询结果存储在变量中：

 select * into demo from maintenanceactivitytrack ; 
    raise notice'p_maintenanceid:%',demo;

- Rinku Choudhary