有没有办法将“cte”临时结果传递给函数？

我已经很久了 CTE 相似部分查询：

_saldo_end as (
    SELECT
      *,
      coalesce( start_debet, 0 ) -coalesce( start_kredit, 0 )
      +coalesce( oborot_deb, 0 ) -coalesce( oborot_kred,  0 ) as _saldo_end
    FROM saldo_start ss
    FULL JOIN schet_oborot( (select * from _schet), daterange(
         (select * from _curr_day),
        ((select * from _curr_day) +interval '1day')::date
    )) o USING ( analitid1 )
),

saldo_end as (
    select *,
      CASE WHEN _saldo_end > 0 THEN  _saldo_end ELSE 0 END as end_debet,
      CASE WHEN _saldo_end < 0 THEN -_saldo_end ELSE 0 END as end_kredit
    from _saldo_end
)

我想为此创建函数：

CREATE OR REPLACE FUNCTION schet_saldo_end( _schet tbuhschet, _period daterange )
RETURNS table( _table setof, analitid1 int, oborot_deb numeric, oborot_kred numeric )
LANGUAGE sql AS $$
WITH
_saldo_start as (
    SELECT
      *,
      coalesce( start_debet, 0 ) -coalesce( start_kredit, 0 )
      +coalesce( oborot_deb, 0 ) -coalesce( oborot_kred,  0 ) as _saldo_end
    FROM _table ss  <<---- I do not know how to pass SETOF/TABLE
    FULL JOIN schet_oborot( (select * from _schet), _period ) o USING ( analitid1 )
),

select analitid1,
  CASE WHEN _saldo_end > 0 THEN  _saldo_end ELSE 0 END as start_debet,
  CASE WHEN _saldo_end < 0 THEN -_saldo_end ELSE 0 END as start_kredit
from _saldo_start
$$

所以我的 CTE 看起来像：

WITH

saldo_init as (
    select
      analitid1,
      sumdeb  as start_debet,
      sumkred as start_kredit
    from saldoanal
    where  schet  = (select * from _schet)
      and nyear  = extract( year  from (select * from _prev_mon) )
      and nmonth = extract( month from (select * from _prev_mon) )
),

saldo_start as (
    select *
    from schet_saldo_end( 'saldo_init', 681, daterange( '2020-06-01', '2020-06-10' ) )
),

saldo_end as (
    select *
    from schet_saldo_end( 'saldo_start', 681, daterange( '2020-06-10', '2020-06-11' ) )
),

select * from saldo_end;

我的问题是 saldo_start 以及 saldo_init 不是真的table，所以我不能传他们的名字。
有办法过去吗 CTE 暂时的结果起作用或引用某些东西 CTE 所以我可以从中选择？
升级版
解决方案之一可能是将ctes结果插入临时表，并将此临时表的名称传递给函数。但我还无法想象如何完成这个

我不知道为什么不能通过 CTEs 结果到函数。事实上那只是一些 C 我想是指向一些数据的指针。
但我们可以储存 CTEs 将结果放入临时表中，然后参考此 TEMP TABLE 从我们的职能。
功能定义为：

--CREATE OR REPLACE FUNCTION schet_saldo_end( _tbl anyelement, _schet tbuhschet, _period daterange )
--RETURNS SETOF anyelement
CREATE OR REPLACE FUNCTION schet_saldo_end( _tbl regclass, _schet tbuhschet, _period daterange )
RETURNS table( analitid1 int, start_debet numeric, start_kredit numeric )
LANGUAGE plpgsql AS $$ BEGIN
RETURN QUERY EXECUTE FORMAT ('
    WITH
    _saldo_start as (
        SELECT
          *,
          coalesce( start_debet, 0 ) -coalesce( start_kredit, 0 )
          +coalesce( oborot_deb, 0 ) -coalesce( oborot_kred,  0 ) as _saldo_end
        FROM %1$I ss
        FULL JOIN schet_oborot( $1, $2 ) o USING ( analitid1 )
    )
    select analitid1,
      CASE WHEN _saldo_end > 0 THEN  _saldo_end ELSE 0 END as start_debet,
      CASE WHEN _saldo_end < 0 THEN -_saldo_end ELSE 0 END as start_kredit
    from _saldo_start
', /* pg_typeof( _tbl ) */ _tbl ) USING _schet, _period;
END $$

最后查询：
通知 BEGIN 是强制性的！

BEGIN;
create temp table xxx
on commit drop as

WITH
_schet    AS ( select 681::numeric(6,2)    as schet                  ),
_curr_day AS ( select '2020-06-10'::date   as cd                     ),
_curr_mon AS ( select '2020-06-01'::date   as cm                     ),
_prev_mon AS ( select cd -interval '1mon'  as pm      from _curr_day ),

saldo_init as (
    select
      analitid1,
      sumdeb  as start_debet,
      sumkred as start_kredit
    from saldoanal
    where  schet  = (select * from _schet)
      and nyear  = extract( year  from (select * from _prev_mon) )
      and nmonth = extract( month from (select * from _prev_mon) )
)

select * from saldo_init;

create temp table yyy
on commit drop as
select * from schet_saldo_end( 'xxx', 681, daterange( '2020-06-01', '2020-06-10' ) );

select * from schet_saldo_end( 'yyy', 681, daterange( '2020-06-10', '2020-06-11' ) );
COMMIT;

尽管在我解决我的问题（是的，丑陋，但它的作品=））问题仍然是开放的：
这可以通过吗 saldo_init 要发挥作用：

WITH saldo_init as ( ... )
select * from schet_saldo_end( 'saldo_init', ... );

升级版
因为 saldo_init 只是一个查询，当我们把它传递给返回 QUERY （我认为这是一个关键的东西，允许我所说的步骤）
所以在这里 schet_saldo_end 只是扩大而已 saldo_init 就像 SELECT fn( t.* ) 扩展到 SELECT fn( t.a ), fn( t.b ) .
所以

saldo_start as (
    select *
    from schet_saldo_end( 'saldo_init', 681, daterange( '2020-06-01', '2020-06-10' ) )
),

只是：

saldo_start as (
    select *
    from ( 

    -- schet_saldo_end starts here
    -- ##########
    _saldo_start as (
        SELECT
          *,
          coalesce( start_debet, 0 ) -coalesce( start_kredit, 0 )
          +coalesce( oborot_deb, 0 ) -coalesce( oborot_kred,  0 ) as _saldo_end
        FROM ( 

      --saldo_init starts here
      select
        analitid1,
        sumdeb  as start_debet,
        sumkred as start_kredit
      from saldoanal
      where  schet  = (select * from _schet)
        and nyear  = extract( year  from (select * from _prev_mon) )
        and nmonth = extract( month from (select * from _prev_mon) )
      --saldo_init end

) ss
        FULL JOIN schet_oborot( 681, daterange( '2020-06-01', '2020-06-10' ) ) o USING ( analitid1 )
    )
    select analitid1,
      CASE WHEN _saldo_end > 0 THEN  _saldo_end ELSE 0 END as start_debet,
      CASE WHEN _saldo_end < 0 THEN -_saldo_end ELSE 0 END as start_kredit
    from _saldo_start

    --#############
    --schet_saldo_end END
) )

最后一个查询也可以折叠为原始查询 CTEs . 这个“函数调用”可以扩展为：

WITH

saldo_init as (
    select
      analitid1,
      sumdeb  as start_debet,
      sumkred as start_kredit
    from saldoanal
    where  schet  = (select * from _schet)
      and nyear  = extract( year  from (select * from _prev_mon) )
      and nmonth = extract( month from (select * from _prev_mon) )
),

--saldo_start as (
--    select *
--    from schet_saldo_end( 'saldo_init', 681, daterange( '2020-06-01', '2020-06-10' ) )
--),

_saldo_end as (
    SELECT
      *,
      coalesce( start_debet, 0 ) -coalesce( start_kredit, 0 )
      +coalesce( oborot_deb, 0 ) -coalesce( oborot_kred,  0 ) as _saldo_end
    FROM saldo_init ss -- <<<< NOTICE HERE we refer `saldo_init`
    FULL JOIN schet_oborot( 681, daterange( '2020-06-01', '2020-06-10' )) o USING ( analitid1 )
),

saldo_end as (
    select *,
      CASE WHEN _saldo_end > 0 THEN  _saldo_end ELSE 0 END as end_debet,
      CASE WHEN _saldo_end < 0 THEN -_saldo_end ELSE 0 END as end_kredit
    from _saldo_end
),

saldo_start as (
  select * from saldo_end
)

因为 schet_saldo_end 嵌入到主 CTE 我们可以参考的问题 saldo_init ;-) (见上文），没有违反任何规则。可能在postgresql中应该实现这种特殊的函数返回优化 QUERY .
希望大家现在都明白我的想法。

有没有办法将“cte”临时结果传递给函数？

1条答案

相关问题

热门标签

最新问答