My full function works fine as a standalone python script, but I was having
trouble getting it to work in Posgres.
Outside of Postgres it worked fine (because I was feeding in lists of dates
to test on).
The I did the script with psycopg2 calling in the below table and it worked
fine. (in the docs for psycopg2 it says it converts the timestamps into
python dates, this would explain why it is working in this scenario)
I finally figured out what the issue is, when using plpython and pulling a
date out of Postgres, it is treating it as text - is that intended or am I
doing something wrong?
Here is a quick/simple example to demonstrate:
--##################################
CREATE OR REPLACE FUNCTION some_test(subject_a timestamp without time
zone[], bt_len integer)
RETURNS timestamp without time zone AS
$BODY$
from datetime import datetime, timedelta
defined_period = timedelta(days=bt_len)
return subject_a[0] + defined_period
$BODY$
LANGUAGE plpythonu;
create table hold_dates
(
initials timestamp without time zone[]
);
insert into hold_dates values('{2014-01-09 10:10:03, 2014-02-18 10:10:03}');
select
some_test(initials, 2)
from
hold_dates;
--##################################
The result of the above is:
ERROR: TypeError: cannot concatenate 'str' and 'datetime.timedelta' objects
CONTEXT: Traceback (most recent call last):
PL/Python function "some_test", line 5, in <module>
return subject_a[0] + defined_period
PL/Python function "some_test"
So the question is - why is plpython returning subject_a[0] as text rather
than an actual date that python can operate on?, is that intended? (trying
to learn how to think about this is a Postgres way)
Granted,
There is a workaround, I can just change my function to read:
return datetime.strptime(subject_a[0], '%Y-%m-%d %H:%M:%S') + defined_period
instead of:
return subject_a[0] + defined_period
However,
I am just trying to figure out why if postgres knows it is a day, it is not
telling python that in the function - like it would when I use psycopg2?
