Skip site navigation (1) Skip section navigation (2)

inconsistent automatic casting between psql and function

From: "Stefano Buliani" <stefano(at)covestor(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: inconsistent automatic casting between psql and function
Date: 2008-12-09 17:28:21
Message-ID: C2DD0BCD6CB34E89A275C7E268369C60@stephlaptop (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
Hello all,

I'm experiencing a strange problem with postgresql 8.3.4.

I have the following table

txid serial
securityid integer
portfolioid integer
datequeued timestamp default now()
tradedate date
numshares numeric(25,7)
transactiontype char(1)
tradeprice numeric(25,7)

every time a new tx is created in the table I need to check if it's the result of 2 previous transaction being aggregated:

I receive 2 tx with the following values:
securityid, portfolioid, tradedate, numshares, transactiontype, tradeprice
2, 1, '2008-12-08', 2, 'B', 15.23
2, 1, '2008-12-08', 6, 'B', 15.23

Later I'll receive another tx:
2, 1, '2008-12-08', 8, 'B', 15.23

This isn't a new trade but just the sum of the previous 2 it should be therefore ignored.

To create the tx in the table I use a function which receives all the values and runs the following query to check whether it's a sum of previous txs. (The tradedate is passed as a timestamp)

 SELECT INTO  vpsum sub1.possible_sum
        FROM (
          SELECT tq.securityid, date_trunc('hour', tq.datequeued) AS split_tq_time, count(*) AS cnt,
          sum(tq.numshares) as possible_sum, tq.transactiontype, tq.tradeprice, tq.portfolioid
          FROM tx_queue AS tq
          WHERE tq.securityid= 2
            AND tq.tradeprice = 15.23
            AND tq.portfolioid = 1
            AND tq.tradedate = '2008-12-08 02:00:00'
            AND tq.datequeued + interval '1 hour' <= now() -- tx received more than 1 hour ago
          GROUP BY date_trunc('hour', tq.datequeued), tq.securityid, tq.portfolioid, tq.tradeprice, tq.transactiontype  
          HAVING count(*)>1
        ) AS sub1
        WHERE sub1.possible_sum = 8.0000000
        ORDER BY sub1.split_tq_time DESC
        LIMIT 1;

If I run this query from the psql client it works just fine. From the function it doesn't return anything.
What I discovered is that for it to work from the function I need to explicitly cast the tradedate variable to DATE (ie '2008-12-08 02:00:00'::DATE - Note the field is of type date).

It would seem that the psql client casts the value automatically. 
Any reason why this should be? 
This inconsistent behaviour makes code much harder to debug.


Stefano Buliani

This message is intended solely for the recipient(s) to whom it is addressed.  If you are not the intended recipient, you should not disclose, distribute or copy this email.  Please notify the sender immediately and delete this email from your system.


pgsql-sql by date

Next:From: Frank BaxDate: 2008-12-09 17:57:15
Subject: Re: store pdf files
Previous:From: Richard HuxtonDate: 2008-12-09 16:12:47
Subject: Re: A DISTINCT problem removing duplicates

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group