在PostgreSQL函数中声明并返回自定义类型

Question

在PostgreSQL函数中声明并返回自定义类型

sqlfunctionpostgresqlstored-proceduresplpgsql

4

我找到了这篇文章：

http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

我是一名有用的助手，可以为您进行文本翻译。以下是需要翻译的内容：

我正在尝试将其用作我的函数示例。我正在从不同的表中选择不同的列，并尝试返回一组记录。

这是我的代码：

CREATE OR REPLACE FUNCTION get_details_for_widget(widgetid integer)
  RETURNS SETOF widgetdetails AS
$BODY$
DECLARE
    rec widgetdetails %rowtype;

BEGIN

    FOR rec IN (

        SELECT widget_details.id, widget_details.contact_id, widget_details.priority, widget_owner.contact
        FROM widget_details, widget_owner
        WHERE widget_details.rid=widgetid 
        AND widget_details.active_yn = 't'
        AND widget_owner.id=widget_details.contact_id
        Order by widget_details.priority ASC)
    LOOP
       RETURN NEXT rec;
    END LOOP;
END;

$BODY$
  LANGUAGE plpgsql;

当我尝试编译这段代码时，出现了“widgetdetails”类型不存在的错误。根据维基百科中的示例，我将逻辑更改为以下内容：

CREATE OR REPLACE FUNCTION get_details_for_widget(widgetid integer)
  RETURNS SETOF widgetdetails AS
            'SELECT widget_details.id, widget_details.contact_id,      
            widget_details.priority, widget_owner.contact
        FROM widget_details, widget_owner
        WHERE widget_details.rid=widgetid 
        AND widget_details.active_yn = "t"
        AND widget_owner.id=widget_details.contact_id
        Order by widget_details.priority ASC'
$BODY$
DECLARE
    rec widgetdetails %rowtype;

BEGIN

    FOR rec IN (

        SELECT widget_details.id, widget_details.contact_id, widget_details.priority, widget_owner.contact
        FROM widget_details, widget_owner
        WHERE widget_details.rid=widgetid 
        AND widget_details.active_yn = 't'
        AND widget_owner.id=widget_details.contact_id
        Order by widget_details.priority ASC)
    LOOP
       RETURN NEXT rec;
    END LOOP;
END;

$BODY$
  LANGUAGE plpgsql;

它给我一个错误提示：

错误："$BODY$附近有语法错误。

但是我似乎看不到/找到问题所在。

- mark li

1

我找到了这篇帖子，并将其用作示例：https://dev59.com/rGoy5IYBdhLWcg3wYMyB 谢谢。 - mark li

2个回答

2

为了使用自定义的复合类型，您需要首先使用 CREATE TYPE 创建它。

以下是基于您的查询的示例（将使用实际数据类型进行更改）：

CREATE TYPE widgetdetails AS (
  id INT,
  contact_id INT,
  widget_details INT,
  contact TEXT
);

只有创建了此类型，这个声明才能被接受：

DECLARE
    rec widgetdetails;
...

对于CREATE FUNCTION的语法，请坚持您的第一版本，第二次尝试存在严重问题，并不能帮助解决问题。

- Daniel Vérité

1

在 rec widgetdetails%rowtype; 中不需要使用 %rowtype。%rowtype 用于从现有表中获取行类型。 - Ihor Romanchenko

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

您尝试使用的语法对于Postgres来说是陌生的。

您的代码比必要的复杂得多。请使用简单的SQL函数：

CREATE OR REPLACE FUNCTION get_details_for_widget(widgetid integer)
  RETURNS TABLE (id int, contact_id int, priority int, contact text)
$func$
   SELECT d.id, d.contact_id, d.priority, o.contact
   FROM   widget_details d
   JOIN   widget_owner   o ON o.id = d.contact_id
   WHERE  d.rid = widgetid   -- where does widgetid come from?
   AND    d.active_yn = 't'
   ORDER  BY d.priority
$func$ LANGUAGE sql

你不需要使用plpgsql来创建这样一个简单的函数，可以使用普通的SQL函数代替。

定义一个带有RETURNS TABLE ()的临时行类型。由于你没有提供表定义，我根据列类型进行了 improvisation。这同样适用于plpgsql函数。

此外：

为了更好的可读性，请使用正确的JOIN条件。
使用表别名简化查询。
对于widget_details.active_yn，请使用数据类型boolean。

布尔值

如评论所述，这已经是一个布尔列。建议使用TRUE / FALSE而不是字符串文字't' / 'f'进行数据输入 - 引用关于布尔类型的手册:

关键字TRUE和FALSE是首选（符合SQL标准）的用法。

在WHERE子句中，每个表达式都会评估为boolean结果。TRUE合格，FALSE或NULL则不行。因此，对于boolean类型，您可以简化：

   AND    d.active_yn = TRUE

仅仅：

   AND    d.active_yn