如何返回在 1 个函数中找到的总行数记录

发布于 2024-10-08 22:58:40 字数 567 浏览 5 评论 0原文

CREATE OR REPLACE FUNCTION some_function(_limit integer, _skip integer, _sortcolumn text, _sortasc boolean)
  RETURNS SETOF some_table AS
$BODY$

begin

 return query execute 'select * from some_table order by "'||_sortcolumn||'"' ||case when _sortasc then 'asc' else 'desc' end ||' limit $1 offset $2;' using _limit, _skip;

end;
$BODY$
  LANGUAGE plpgsql STABLE SECURITY DEFINER
  COST 100
  ROWS 100;

我想通过引用传递参数，因此我可以将总行数分配给参数。到目前为止我觉得这是不可能的。有什么建议吗？我正在使用 C#

编辑，

我发现“RAISE NOTICE”可能有用。仍然找到一种通过 npgsql 在 .Net 中接收通知的方法

原文

CREATE OR REPLACE FUNCTION some_function(_limit integer, _skip integer, _sortcolumn text, _sortasc boolean)
  RETURNS SETOF some_table AS
$BODY$

begin

 return query execute 'select * from some_table order by "'||_sortcolumn||'"' ||case when _sortasc then 'asc' else 'desc' end ||' limit $1 offset $2;' using _limit, _skip;

end;
$BODY$
  LANGUAGE plpgsql STABLE SECURITY DEFINER
  COST 100
  ROWS 100;

i want to pass parameter by reference, so i can assign total rows to param. so far i find it impossible. any suggestion? i'm using C#

edit

i found 'RAISE NOTICE' probably useful. still find a way to recieve notice in .Net via npgsql

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦年海沫深 2024-10-15 22:58:40

有几种方法可以做到这一点。如果您直接使用命令对象调用此函数，则 ExecuteScalar 函数将返回命令返回的行数。

rowCountValue = (int)command.ExecuteScalar();

或者，您可以在函数定义中执行此操作；

CREATE OR REPLACE FUNCTION some_function(_limit integer, _skip integer, _sortcolumn text, _sortasc boolean, OUT _row_count integer)
  RETURNS SETOF some_table AS
$BODY$
DECLARE
    _result RECORD;
BEGIN
EXECUTE 'SELECT * FROM some_table ORDER BY "'||_sortcolumn||'"' ||case when _sortasc then 'asc' else 'desc' end ||' limit $1 offset $2;' INTO _result using _limit, _skip;

GET DIAGNOSTICS _row_count = ROW_COUNT;

return result;

END;
$BODY$
  LANGUAGE plpgsql STABLE SECURITY DEFINER
  COST 100
  ROWS 100;

如果你询问建筑没问题，那么这个应该工作得很好。

希望这有帮助。

There are several ways to do that. If you are calling this function directly using command object, the ExecuteScalar function returns the number of row returned by the command.

rowCountValue = (int)command.ExecuteScalar();

Or, you can do it the in the function definition;

CREATE OR REPLACE FUNCTION some_function(_limit integer, _skip integer, _sortcolumn text, _sortasc boolean, OUT _row_count integer)
  RETURNS SETOF some_table AS
$BODY$
DECLARE
    _result RECORD;
BEGIN
EXECUTE 'SELECT * FROM some_table ORDER BY "'||_sortcolumn||'"' ||case when _sortasc then 'asc' else 'desc' end ||' limit $1 offset $2;' INTO _result using _limit, _skip;

GET DIAGNOSTICS _row_count = ROW_COUNT;

return result;

END;
$BODY$
  LANGUAGE plpgsql STABLE SECURITY DEFINER
  COST 100
  ROWS 100;

If you query building is ok, then this one should work quite well.

Hope this helps.

回复收藏 0 原文