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

Question

我对类似部分的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不是真正的表，所以我不能传递他们的名字。

有没有办法将CTE临时结果传递给函数或引用一些CTE，以便我可以从中选择？

UPD

可能的解决方案之一是将 CTE 结果插入 TEMP TABLE，并将此 TEMP TABLE 的名称传递给函数。但我无法想象如何完成这个

标签： sqlpostgresqlcommon-table-expression

Answer 1

我不知道为什么不能将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 $$

最后查询：
NOTICE 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', ... );

UPD
因为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
) )

这个最终查询也可以折叠为 original 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。

希望大家现在明白我的想法。