首页 \ 问答 \ 为什么我的plpgsql函数没有返回任何行(why does my plpgsql function return no rows)

为什么我的plpgsql函数没有返回任何行(why does my plpgsql function return no rows)

当两个查询针对用户独立运行时,我得到结果300和0,但是在我的函数中,我没有返回任何行。

我正在从SQL Server迁移到PostgresSQL,所以有些事情对我来说有些陌生!

create or replace function getsummary(userid int)
returns table (assetTotal numeric, liabilityTotal numeric)
as $$
BEGIN

SELECT sum(t.credit) - sum(t.debit)
into assetTotal
    from transactions t
    join user_institutes i on t.user_institute_id = i.id
    where i.account_type in (1,3,4,5)
    and i.user_id = userid;

    select sum(t.credit) - sum(t.debit)
    into liabilityTotal
    from transactions t
    join user_institutes i on t.user_institute_id = i.id
    where i.account_type in (2,8,10)
    and i.user_id = userid;
END
$$ language plpgsql;

select * from getsummary(1)

非常感谢


when the two queries are run independently against a user I get the results 300 and 0 however in my function I am getting no rows returned.

I am moving from SQL Server to PostgresSQL so some things are a bit alien to me!

create or replace function getsummary(userid int)
returns table (assetTotal numeric, liabilityTotal numeric)
as $$
BEGIN

SELECT sum(t.credit) - sum(t.debit)
into assetTotal
    from transactions t
    join user_institutes i on t.user_institute_id = i.id
    where i.account_type in (1,3,4,5)
    and i.user_id = userid;

    select sum(t.credit) - sum(t.debit)
    into liabilityTotal
    from transactions t
    join user_institutes i on t.user_institute_id = i.id
    where i.account_type in (2,8,10)
    and i.user_id = userid;
END
$$ language plpgsql;

select * from getsummary(1)

Many thanks


原文:https://stackoverflow.com/questions/43824847
更新时间:2022-12-05 09:12

最满意答案

没有深入查询您的查询,只是指出缺少部分。 尝试?:

create or replace function getsummary(userid int)
returns table (assetTotal numeric, liabilityTotal numeric)
as $$
DECLARE
 _assetTotal numeric;
 _liabilityTotal numeric;
BEGIN

SELECT sum(t.credit) - sum(t.debit)
into _assetTotal
    from transactions t
    join user_institutes i on t.user_institute_id = i.id
    where i.account_type in (1,3,4,5)
    and i.user_id = userid;

    select sum(t.credit) - sum(t.debit)
    into _liabilityTotal
    from transactions t
    join user_institutes i on t.user_institute_id = i.id
    where i.account_type in (2,8,10)
    and i.user_id = userid;
  return query select _assetTotal, _liabilityTotal;
END
$$ language plpgsql;

not looking deep to your queries, just pointing missing part. try?:

create or replace function getsummary(userid int)
returns table (assetTotal numeric, liabilityTotal numeric)
as $$
DECLARE
 _assetTotal numeric;
 _liabilityTotal numeric;
BEGIN

SELECT sum(t.credit) - sum(t.debit)
into _assetTotal
    from transactions t
    join user_institutes i on t.user_institute_id = i.id
    where i.account_type in (1,3,4,5)
    and i.user_id = userid;

    select sum(t.credit) - sum(t.debit)
    into _liabilityTotal
    from transactions t
    join user_institutes i on t.user_institute_id = i.id
    where i.account_type in (2,8,10)
    and i.user_id = userid;
  return query select _assetTotal, _liabilityTotal;
END
$$ language plpgsql;

相关问答

更多

相关文章

更多

最新问答

更多
  • Apache HttpClient(4.1和更新版本):如何进行基本身份验证?(Apache HttpClient (4.1 and newer): how to do basic authentication?)
  • 复选框不适用于模态(Checkbox does not work in modal)
  • 当使用大标题无法正常工作时,iOS 11会滚动到顶部(iOS 11 scroll to top when using large titles doesn't work properly)
  • 为什么我的Arduino不能超过10?(Why can't my Arduino compare above 10?)
  • 从Lib中删除PDB文件/功能(Remove the PDB file/functionality from Lib)
  • rails-2.3.5和rack-1.0.1的来源在哪里?(Where are the sources for rails-2.3.5 and rack-1.0.1?)
  • 如何使用List <>成员不可变的类?(How to make a class with List<> member immutable?)
  • Microsoft Edge popup扩展如何获取后台页面?(Microsoft Edge popup extension how to get backgroundpage?)
  • python删除中文unicode字符串之间的空格,但不删除英文单词之间的空格(python to remove space between Chinese unicode strings but not between English words)
  • SBT:如何Dockerize一个胖罐子?(SBT: How to Dockerize a fat jar?)
  • 如何从服务器请求中保存图像的分区以便以后合并(How to save segements of image in memory from server request for merging later)
  • python这段程序中a=self.[:]是什么意思 self一般用法是啥
  • ActiveRecord :: StatementInvalid:PGError:错误:关系“指令”不存在(ActiveRecord::StatementInvalid: PGError: ERROR: relation “instructions” does not exist)
  • 虚拟机安装kail linux最低配置是多少?
  • fread()中的空字符和c中的strncpy()(Null character in fread() and strncpy() in c)
  • 从ShutdownHook中查找程序退出的原因(Find, from a ShutdownHook, why a program exits)
  • 每天在固定时间运行任务(Run a task at fixed time everyday)
  • 电脑开不了机了,开机后进了桌面,显示WINDOWS BOOT MANAGER
  • 获取字符串第n个出现的索引?(Get the index of the nth occurrence of a string?)
  • python selenium 怎么打开 Chrome 并且能设置代理,请问可以指导小弟一下吗。
  • 错误:imagecopymerge()期望参数2是资源(Error: imagecopymerge() expects parameter 2 to be resource)
  • 在我的代码中,“无法找到可安装的ISAM”错误的原因是什么?(What could be the cause of 'Could not find installable ISAM' error in my code?)
  • Access 2013 - 没有自动编号的顺序编号(Access 2013 - sequential numbering without autonumber)
  • 将带有函数的Javascript对象转换为字符串(Converting a Javascript Object with Functions into a String)
  • Java中实现封装与信息隐藏与抽象与隐藏数据的比较(Practical example Encapsulation vs Information Hiding vs Abstraction vs Data Hiding in Java)
  • 不要包必须匹配java文件所在的子目录吗?(Don't packages have to match the subdirectories the java file is in?)
  • PyCharm Python控制台中的文件路径错误(File path wrong in PyCharm Python Console)
  • 在数据框列上应用curve_fit(Apply curve_fit on dataframe columns)
  • Java中的DateTime.FromOADate()相当于什么(Java中的Datetime是Datetime的两倍)(What is the equivalent of DateTime.FromOADate() in Java (double to Datetime in Java))
  • 给定DCEL,其中双胞胎等于下一个边缘,细分可以有多少面?(Given a DCEL where the twin is equal to the next of an edge, how many faces can the subdivision have?)