Variables inside plpythonu

On 05/11/2012 11:43 PM, Frank Lanitz wrote:

Hi folks,

I did check the documentation but seem to didn't found the best way to
use plpythonu with a variable inside a query.
Let's say I want to run this query
SELECT id FROM some_table WHERE date=<date_from_function_call>
How a CREATE FUNCTION stateent have to look like?
I already figured out that the python code should look similar to

plan = plpy.prepare("SELECT id FROM some_table WHERE date=????
return = plpy.execure(plan)

But somehow a last piece is missing.

Something like this?:

create or replace function date_test(some_date date) returns void as
$Body$
date_plan = plpy.prepare("select id_fld from date_test where date_fld = $1", ["date"])
date_rs = plpy.execute(date_plan,[some_date])
plpy.notice(date_rs[0]["id_fld"])
$Body$
language plpythonu;

test=# SELECT * from date_test where date_fld='2008-08-28'::date;
id_fld | date_fld | date_per | date_qty | line_id
--------+------------+----------+----------+---------
1 | 2008-08-28 | | | 14
1 | 2008-08-28 | | 1 day | 15
1 | 2008-08-28 | | | 14
1 | 2008-08-28 | | 1 day | 15
1 | 2008-08-28 | | | 14
1 | 2008-08-28 | | 1 day | 15

test=# SELECT * from date_test('2008-08-28'::date);
NOTICE: {'id_fld': 1}
CONTEXT: PL/Python function "date_test"
date_test
-----------

(1 row)

Can anybody help?

Cheers,
Frank

--
Adrian Klaver
adrian.klaver@gmail.com

On Sat, 12 May 2012 07:11:08 -0700
Adrian Klaver <adrian.klaver@gmail.com> wrote:

Something like this?:

create or replace function date_test(some_date date) returns void as
$Body$
date_plan = plpy.prepare("select id_fld from date_test where date_fld
= $1", ["date"]) date_rs = plpy.execute(date_plan,[some_date])
plpy.notice(date_rs[0]["id_fld"])
$Body$
language plpythonu;

Yes. Gave me the missing piece. Thanks a lot!

Cheers,
Frank
--
Frank Lanitz <frank@frank.uvena.de>