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

Question

我找到这篇文章:

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”不存在。根据 wiki 中的示例，我将逻辑更改为如下所示:

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;

它给了我一个错误:

ERROR: syntax error at or near "$BODY$

但我似乎看不到/发现问题。

Answer 1

您尝试使用的语法对于 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 function相反。

使用 RETURNS TABLE () 定义临时行类型 。因为您没有提供表定义，所以我临时设置了列类型。这也适用于 plpgsql 函数。

另外:

• 使用正确的JOIN condition为了更好的可读性。

• 使用表别名简化查询。

• 使用数据类型 boolean对于 widget_details.active_yn。

bool 值

正如评论中所澄清的，它已经是一个 bool 列。我建议使用 TRUE/FALSE 而不是字符串文字 't'/'f' 进行数据输入 - 引用 the manual about the boolean type :

The key words TRUE and FALSE are the preferred (SQL-compliant) usage.

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

   AND    d.active_yn = TRUE

只是:

   AND    d.active_yn