PostgreSQL -声明变量的正确方法

Question

我有以下postgresql查询，该查询是为在生产数据库中使用以返回每日计数记录而创建的。此查询在数据库的“暂存”/test版本中运行并返回正确的结果，但是当在生产数据库上运行时，不会返回任何结果。我怀疑是我用来完成这个任务的方法最终导致了我的问题(写临时表，删除提交，从所述临时表中选择*)。

所以我的问题--如果还有其他更好的方法来实现我在这里想做的事情..它最终能够将"queryDate“声明为变量并在查询中使用它。

几点兴趣-

• 我们需要能够轻松地更改queryDate“变量”，因为它在整个脚本中被多次引用。这就是为什么它的declared.
• queryDate被声明为时间戳，尽管我们输入日期是因为列数据类型是时间戳，尽管需求是能够输入要在查询中使用的日期。根据我在测试期间所见和经验，日期是时间戳类型的有效输入。
• 我创建了一个表"temp_output“，因为没有它，”查询没有结果数据的目的地“。我试图在mssql/tsql中模拟一个临时表。有更好的方法吗？
• --我怀疑"ON COMMIT DROP AS“部分在这里引起了问题--对此有什么看法吗？

任何见解都会受到赞赏。

下面是我正在做的事情的简化版本，或者是寻找一种更好的方法来完成：

DO $$
DECLARE 

--*** MODIFY DATE TO BE USED VVV HERE *** YYYY-MM-DD format
queryDate timestamp := '2020-10-4';


BEGIN
    CREATE TEMP TABLE temp_output ON COMMIT DROP AS
    select distinct on (queryDate)
    date(queryDate) as "Date",
    (
    select
        count(account.id)
        where date(account.created) = queryDate
    ) as "Total Registrations"
    from account
    order by queryDate;
END $$;

SELECT * FROM temp_output;

下面是测试上面最小可复制示例所需的表：

Table: Account

ACCOUNT.ID (PK, Integer)     ACCOUNT.CREATED (Timestamp w/ time zone)
1234                          2020-10-04 17:52:40.340573-04
3245                          2020-10-04 17:53:40.340573-04
2345                          2020-10-04 19:52:40.340573-04
5533                          2020-10-05 17:52:40.340573-04
2288                          2020-10-010 17:52:40.340573-04

例如，预期产出：

Date:               Total Registrations:
10-4-2020              3

以下是完整的查询：

-- Daily Recap Report : 10/30/2020
-- Single row report; totals for given day.

DO $$
DECLARE 

--*** MODIFY DATE TO BE USED VVV HERE *** YYYY-MM-DD format
queryDate timestamp := '2020-10-4';


BEGIN
    CREATE TEMP TABLE temp_output ON COMMIT DROP AS
    select distinct on (queryDate)
    date(queryDate) as "Date",
    (
    select
        count(account.id)
        where date(account.created) = queryDate
    ) as "Total Registrations",
    (
    select
        count(orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and date(account.created) = queryDate
    ) as "Total New User Orders",
    (
    select
        count(orders.id)
        from orders
        where date(orders.placed) = queryDate
    ) as "Total Orders",
    (
    select
        sum(order_item.quantity)
        from order_item
        join orders on orders.id = order_item.order_id
        and date(orders.placed) = queryDate
    ) as "Total Items Sold",
    (
    select
        count (distinct orders.store_id)
        from orders
        where date(orders.placed) = queryDate
    ) as "Cooks with Sales",
    (
    select
        cast(sum(promo_code.value) as money)
        from orders
        join promo_code on promo_code.id = orders.promo_code_id
        where date(promo_code.redeemed) = queryDate
        and date(orders.placed) = queryDate
    ) as "Promo Code Used",
    (
    select
        cast(sum(transaction.sub_total) as money)
        from orders
        left join transaction on transaction.order_id = orders.id
        where date(orders.placed) = queryDate
    ) as "Income Total",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '01%'
    ) as "Álvaro Obregón",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '02%'
    ) as "Azcapotzalcoelse",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '03%'
    ) as "Benito Juárez",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '04%'
    ) as "Coyoacán",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '05%'
    ) as "Cuajimalpa",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '06%'
    ) as "Cuauhtémoc",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '07%'
    ) as "Gustavo A. Madero",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '08%'
    ) as "Iztacalco",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '09%'
    ) as "Iztapalapa",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '10%'
    ) as "Magdalena Contreras",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '11%'
    ) as "Miguel Hidalgo",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '12%'
    ) as "Tlahuac",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '13%'
    ) as "Tlalpan",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '14%'
    ) as "Venustiano Carranza",
    (
    select
        count (orders.id)
        from account
        inner join orders on account.id = orders.buyer_account_id
        where date(orders.placed) = queryDate
        and account.zip_code like '15%'
    ) as "Xochimilco"
    from account
    left join store on account.id = store.account_id
    left join menu on store.id = menu.store_id
    left join menu_item on menu.id = menu_item.menu_id
    left join orders on (orders.store_id = store.id)
    join store_address on store.id = store_address.store_id
    join address on store_address.address_id = address.id
    group by account.id
    order by queryDate;
END $$;

SELECT * FROM temp_output;

Answer 1

下面是使用您的第一个示例和@Abelisto建议的更改的示例代码：

CREATE OR REPLACE FUNCTION cnt_func(cnt_date date)
RETURNS TABLE(the_date date, the_count integer)
   LANGUAGE plpgsql
AS $Body$

BEGIN
    RETURN QUERY SELECT 
        created::date, count(*)
    FROM 
        account
    WHERE
        created::date = cnt_date
    GROUP BY 
        created
    ;
END;

$Body$;


SELECT * FROM cnt_func('11/08/2020'::date);

没有经过测试，但它应该传达一般的想法。