1
\$\begingroup\$

I have the following postgresql query that was created for use in a production database to return a daily recap of counts. This query runs and returns correct results in the "staging"/test version of the database, but when run on the production db, no results are returned. I suspect it's the method I used to accomplish this that is ultimately causing my problems (writing to temp table, dropping on commit and selecting * from said temp table).

So my question- if there's another, better way to achieve what I'm trying to do here.. which is ultimately just to be able to declare "queryDate" as a variable and use it within the query.

A few points of interest-

  • We need to be able to change the queryDate "variable" easily, as it's referenced many times throughout the script. Which is why it's declared.
  • queryDate is declared as timestamp, even though we're inputting dates because that columns data type is timestamp, even though the requirement is to be able to enter a date to be used in the query. From what I've seen as well as experienced during testing, a date is a valid input for timestamp type.
  • I created a table "temp_output" because without it I get "query has no destination for result data". I'm trying to emulate a temporary table in mssql/tsql. Is there a better way to do this?
  • I suspect the "ON COMMIT DROP AS" portion to be causing issue here- any words on that?

Any insights are appreciated.

Here's a simplified version of what I'm doing, or looking for a better way to accomplish:

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;

Here's the table you would need to test the minimal reproducable example above:

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-10 17:52:40.340573-04

Expected output for example:

Date:               Total Registrations:
10-4-2020              3

Here's the query:

-- Daily Recap Report : 10/30/2020
-- Single column 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;
\$\endgroup\$
3
  • 3
    \$\begingroup\$ Two things. First, Code Review isn't designed to tell you why your code is not working as it should. We tell you how to make your code do what it is trying to do in a better fashion. Second, you might get a better answer to this specific question at DBA.SE. \$\endgroup\$
    – mdfst13
    Nov 7, 2020 at 16:45
  • \$\begingroup\$ @mdfst13 yeah I understand that. I probably should have worded differently. I'd really like to know if there's a better way to do what I'm doing here- as I presume it's the method I used that's causing my problems (writing to a temp table and dropping on commit, then selecting from that temp table). \$\endgroup\$
    – boog
    Nov 7, 2020 at 18:30
  • 1
    \$\begingroup\$ According to this documentation a temporary table will be deleted at the end of the stored procedure automatically, so what is the purpose of the on commit drop in your stored procedure? It seems to be deleting the temporary table before the final select statement. I believe you will get more help from the DBA site than you will get from this site. \$\endgroup\$
    – pacmaninbw
    Nov 7, 2020 at 21:27

0

Browse other questions tagged or ask your own question.