在PostgreSQL函数中声明并返回一个自定义类型 [英] Declare and return a custom type in PostgreSQL function

问题描述

我发现这篇文章：

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

我试图用它作为我的函数的一个例子。
我从不同的表中选择不同的列，并尝试返回一组记录。

这里是我的代码：

$ $ p $ code创建或替换函数％行类型;

BEGIN

对于rec IN（

）选择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 $
语言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 $ b $ 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 
 
对于rec IN（
 
）选择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 $ 
语言plpgsql; 
  

它给了我一个错误：

< blockquote>

错误：在$ BODY $

或
$附近出现语法错误b $ b

但是我似乎无法看到/发现问题。

解决方案

您尝试的语法Postgres使用的是外来的。

你的代码比它需要的复杂得多。使用一个简单的SQL函数：

 创建或替换函数get_details_for_widget（widgetid integer）
 RETURNS TABLE（id int，contact_id int，优先级int，联系人文本）
 $ func $ 
 SELECT d.id，d.contact_id，d.priority，o.contact 
从widget_details d 
 JOIN widget_owner o ON o.id = d.contact_id 
 WHERE d.rid = widgetid -  widgetid从哪里来？
 AND d.active_yn ='t'
 ORDER BY d.priority 
 $ func $ LANGUAGE sql 
  

你不需要plpgsql l这样一个简单的功能。改为使用普通的 SQL函数。

使用特别行类型 rel =nofollow> RETURNS TABLE（） 。由于您没有提供表格定义，所以我即兴创作了列类型。这也适用于plpgsql函数。

另外：

• 使用适当的 JOIN 条件以提高可读性。


• 使用表别名简化查询。




• 使用数据类型 布尔 用于 widget_details.active_yn 。

布尔值

正如评论中所阐明的那样，它已经是一个布尔列。我会建议使用 TRUE / FALSE 来代替数据输入的字符串文字't'/'f'引用有关布尔类型的手册：

关键字 TRUE 和 FALSE 是在$ WHERE 子句中，首选的是符合SQL规范的用法。

每个表达式都被计算为一个布尔值结果。 TRUE 限定， FALSE 或 NULL 不。因此，对于布尔型类型，您可以简化：

  AND d .active_yn = TRUE 
  

只需：

  AND d.active_yn 
  

I found this article:

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

and I'm trying to use it as an example for my function. I am selecting different columns from different tables, and trying to return a set of records.

Here's my code:

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;

When I try to compile this code, I get the error the the type "widgetdetails" does not exist. In line with the example in the wiki, I changed my logic to look like this:

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;

It gives me an error that says:

ERROR: syntax error at or near "$BODY$

But I can't seem to see / find the problem.

解决方案

The syntax you are trying to use is foreign to Postgres.

Your code is much more complicated than it needs to be. Use a simple SQL function:

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

You don't need plpgsql at all for such a simple function. Use a plain SQL function instead.

Define an ad-hoc row type with RETURNS TABLE (). I improvised with the column types since you did not provide your table definitions. This works for plpgsql functions just as well.

Also:

• Use a proper JOIN condition for better readability.

• Simplify your query with table aliases.

• Use the data type boolean for widget_details.active_yn.

Boolean values

As clarified in the comment, it's a boolean column already. I would advice to use TRUE / FALSE instead of the string literals 't' / 'f' for data input - quoting the manual about the boolean type:

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

In a WHERE clause, every expression is evaluated to a boolean result. TRUE qualifies, FALSE or NULL do not. So, for a boolean type, you can simplify:

   AND    d.active_yn = TRUE

to just:

   AND    d.active_yn

这篇关于在PostgreSQL函数中声明并返回一个自定义类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！