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?
