inconsistent automatic casting between psql and function - Mailing list pgsql-sql
| From | Stefano Buliani |
|---|---|
| Subject | inconsistent automatic casting between psql and function |
| Date | |
| Msg-id | C2DD0BCD6CB34E89A275C7E268369C60@stephlaptop Whole thread Raw |
| Responses |
Re: inconsistent automatic casting between psql and function
(Richard Huxton <dev@archonet.com>)
|
| List | pgsql-sql |
Hello all,
I'm experiencing a strange problem with postgresql 8.3.4.
I have the following table
tx_queue
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
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
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
) AS sub1
WHERE sub1.possible_sum = 8.0000000
ORDER BY sub1.split_tq_time DESC
LIMIT 1;
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.
Regards,
Stefano
---------------------------------
Stefano Buliani
Covestor
Stefano Buliani
Covestor
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.