在PostgreSQL中返回QUERY-Record

发布时间：2020-12-13 16:05:27 所属栏目：百科来源：网络整理

导读：我正在尝试编写PostgreSQL函数,该函数在数据库中插入数据,然后接收一些数据并返回它. 这是代码： CREATE OR REPLACE FUNCTION newTask(projectid api.objects.projectid%TYPE,predecessortaskid api.objects.predecessortaskid%TYPE,creatoruserid api.objec

我正在尝试编写PostgreSQL函数,该函数在数据库中插入数据,然后接收一些数据并返回它.
这是代码：

CREATE OR REPLACE FUNCTION newTask(projectid api.objects.projectid%TYPE,predecessortaskid api.objects.predecessortaskid%TYPE,creatoruserid api.objects.creatoruserid%TYPE,title api.objects.title%TYPE,description api.objects.description%TYPE,deadline api.objects.deadline%TYPE,creationdate api.objects.creationdate%TYPE,issingletask api.tasks.issingletask%TYPE)
  RETURNS SETOF api.v_task AS
$$
DECLARE
    v_objectid api.objects.objectid%TYPE;
BEGIN
   INSERT INTO api.objects(objectid,projectid,predecessortaskid,creatoruserid,title,description,deadline,creationdate) VALUES (DEFAULT,creationdate)
   RETURNING objectid INTO v_objectid;

   INSERT INTO api.tasks(objectid,issingletask) VALUES (v_objectid,issingletask);
   RETURN QUERY (SELECT * FROM api.v_task WHERE objectid = v_objectid);
END;
$$LANGUAGE plpgsql;

对象和任务都是表,v_task是一个视图,它是两者的连接.我返回刚刚插入的数据的原因是有一些触发器在它上面工作.

到现在为止还挺好.我使用RETURNS SETOF api.v_task作为我的返回类型和RETURN QUERY(…),因此期望结果看起来像是来自v_task的SELECT(具有相同数据类型的相同列).然而,真正发生的是(从pgAdmin输出,来自我的node.js-application的相同结果)：

SELECT newTask( CAST(NULL AS integer),CAST(NULL AS integer),1,varchar 'a',cast(NOW() as timestamp(0) without time zone),true);

newtask
api.v_task
--------
"(27,a,"2012-03-19 12:15:50","2012-03-19 12:15:49.629997",t)"

而不是几列,输出被强制为一个,用逗号分隔.
由于我已经在使用特殊记录类型,因此无法使用AS关键字指定输出的字段.

解决方法

调用表函数

要从返回多个列的函数(实际上是复合类型或行类型)中检索单个列,请使用以下命令调用它：

SELECT * FROM func();

如果你愿意,你也可以只选择一些列,而不是其他列.想象一下像表这样的函数(也称为表函数)：

SELECT objectid,title FROM func();

替代方案：纯SQL

如果您使用PostgreSQL 9.1或更高版本,您可能会对此变体感兴趣.我使用可写CTE链接INSERT.

可能有人试图将最终的SELECT作为另一个模块添加到CTE,但在这种情况下不起作用,因为新插入的值在同一CTE中的视图中不可见.所以我把它作为一个单独的命令 – 在SELECT周围没有括号：

CREATE OR REPLACE FUNCTION new_task (
    _projectid         api.objects.projectid%TYPE,_predecessortaskid api.objects.predecessortaskid%TYPE,_creatoruserid     api.objects.creatoruserid%TYPE,_title             api.objects.title%TYPE,_description       api.objects.description%TYPE,_deadline          api.objects.deadline%TYPE,_creationdate      api.objects.creationdate%TYPE,_issingletask      api.tasks.issingletask%TYPE)
  RETURNS SETOF api.v_task AS
$func$
DECLARE
   _objectid api.objects.objectid%TYPE;
BEGIN
   RETURN QUERY
   WITH x AS (
      INSERT INTO api.objects
             ( projectid,creationdate)
      VALUES (_projectid,_predecessortaskid,_creatoruserid,_title,_description,_deadline,_creationdate)
      RETURNING objectid
      )
   INSERT INTO api.tasks
           (objectid,issingletask)
   SELECT x.objectid,_issingletask
   FROM   x
   RETURNING objectid INTO _objectid;

   RETURN QUERY
   SELECT * FROM api.v_task WHERE objectid = _objectid;
END
$func$LANGUAGE plpgsql;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!