选择“存储过程中的联合”

Question

>我使用 postgresql 创建了这个存储过程：

 create or replace Function GetTimeStamp(
    IN t1 timestamp ,
    IN t2 timestamp ,
    Out reg_os Varchar(50),
    Out customer_count int ,
    Out t5 timestamp
    ) returns void AS $$
    Begin
    SELECT payment.payment_ts AS t , customer.registration_os As os ,count(Distinct customer.customer_id) As total
    From customer inner join payment on customer.customer_id = payment.customer_id
    Where Payment.payment_ts Between t1 And t2 
    Group By 1 , 2
    Union
    SELECT transfer.ts_created AS t , customer.registration_os As os, count(Distinct customer.customer_id) As total 
    From customer inner join account on customer.customer_id = account.customer_id inner join transfer on account.account_id = transfer.from_account
    Where transfer.ts_created Between t1 And t2 
    Group By 1 , 2;
    select t , os , total into t5 , reg_os , customer_count ;
    End;
    $$ LANGUAGE plpgsql;

我可以创建存储过程，但是当我使用此代码时：

 SELECT GetTimeStamp('2016-01-01' ,'2017-01-01')   OR  
 SELECT * FROM GetTimeStamp('2016-01-01' ,'2017-01-01')

它给了我一个错误：

查询没有结果数据的目标 提示：如果要丢弃 选择的结果，请改用执行

.

我在存储过程中使用了执行而不是选择，它给了我

执行附近的语法错误。

我不知道我是否可以在两个执行查询之间使用联合。如何获取此存储过程的结果？

Answer 1

在 PL/pgSQL 中，你需要使用 SELECT ... INTO 来指定应该存储结果的变量。要放弃结果，请使用 PERFORM 而不是 SELECT 。

Answer 2

你需要返回一个集合（table），它可以是普通的SQL

create or replace Function GetTimeStamp (
   IN t1 timestamp ,
   IN t2 timestamp
) returns table (
    reg_os Varchar(50),
    customer_count int,
    t5 timestamp
) language sql as $$
    SELECT
        payment.payment_ts AS t ,
        customer.registration_os As os ,
        count(Distinct customer.customer_id) As total
    From
        customer
        inner join
        payment on customer.customer_id = payment.customer_id
    Where Payment.payment_ts Between t1 And t2 
    Group By 1 , 2
    Union
    SELECT
        transfer.ts_created AS t ,
        customer.registration_os As os,
        count(Distinct customer.customer_id) As total 
    From
        customer
        inner join
        account on customer.customer_id = account.customer_id
        inner join
        transfer on account.account_id = transfer.from_account
    Where transfer.ts_created Between t1 And t2 
    Group By 1 , 2;
$$;