问题创建PLPGSQL函数，该函数接受数组作为输入，并从表中返回setof记录

发布于 2025-01-30 06:17:35 字数 854 浏览 3 评论 0原文

我正在尝试创建功能，该功能将接受数组为输入，然后返回数组中每个参数的setof记录。我有table country_rigions，由3列组成：ID int，region_name text，country_name text;

我的功能代码看起来像这样：

CREATE OR REPLACE FUNCTION search1(TEXT[])
RETURNS SETOF RECORD AS $$

DECLARE x RECORD;

BEGIN 
    FOR x IN
        SELECT *
        FROM company_regions
        WHERE country_name = $1::TEXT
        LOOP
        RETURN NEXT x;
        END LOOP;
END; $$
LANGUAGE plpgSQL;

此功能成功创建了，但是当我尝试以这样的方式调用函数时：

SELECT * FROM search1(ARRAY ['usa', 'canada']) AS search1(id int, region_name TEXT, country_name text)

它返回带有0行的表。

有人可以告诉我我在做什么错？我是SQL的新手，试图在其他帖子中找到答案，但我仍然无法弄清楚这个问题。

原文

I'm trying to create function which will accept ARRAY as INPUT and then return SETOF RECORD for each of the parameter in ARRAY.
I have table country_regions which consists of 3 Columns: id int, region_name TEXT, country_name TEXT;

My Functions code looks like this:

CREATE OR REPLACE FUNCTION search1(TEXT[])
RETURNS SETOF RECORD AS $

DECLARE x RECORD;

BEGIN 
    FOR x IN
        SELECT *
        FROM company_regions
        WHERE country_name = $1::TEXT
        LOOP
        RETURN NEXT x;
        END LOOP;
END; $
LANGUAGE plpgSQL;

This Function was created successfully, but when I try to call the function like this:

SELECT * FROM search1(ARRAY ['usa', 'canada']) AS search1(id int, region_name TEXT, country_name text)

it returns table with 0 rows in it.

Can someone tell me what am I doing wrong? I'm completely new to SQL, tried to find answer in other post but I still could not figure out the problem.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

回梦 2025-02-06 06:17:35

您尝试将文本值与文本[]进行比较。

CREATE OR REPLACE FUNCTION search1(text[])
RETURNS SETOF company_regions AS $
BEGIN
  RETURN QUERY SELECT * FROM company_regions
                 WHERE country_name = ANY($1);
END
$ LANGUAGE plpgsql STABLE

注意 - 这样的功能是优化器的黑匣子。通常，使用一个SQL语句的信封（从性能角度来看）不太好（从性能角度来看）。在复杂的查询中，它可以阻止一些优化（主要是如果您忘记设置函数的正确标志 - 在这种情况下稳定）。

You try to compare text value versus text[].

CREATE OR REPLACE FUNCTION search1(text[])
RETURNS SETOF company_regions AS $
BEGIN
  RETURN QUERY SELECT * FROM company_regions
                 WHERE country_name = ANY($1);
END
$ LANGUAGE plpgsql STABLE

Attention - functions like this are black box for optimizer. Usually is not too good (from performance perspective) using functions like envelops of one SQL statement. In complex query it can block some optimizations (Mainly if you forget to set correct flag of function - in this case STABLE).

回复收藏 0 原文

~没有更多了~