Re: Variables inside plpythonu - Mailing list pgsql-general

From Adrian Klaver
Subject Re: Variables inside plpythonu
Date
Msg-id 4FAE6F7C.5050807@gmail.com
Whole thread Raw
In response to Variables inside plpythonu  (Frank Lanitz <frank@frank.uvena.de>)
Responses Re: Variables inside plpythonu
List pgsql-general
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

pgsql-general by date:

Previous
From: Boszormenyi Zoltan
Date:
Subject: Re: Lock out PostgreSQL users for maintenance
Next
From: Frank Lanitz
Date:
Subject: Re: Variables inside plpythonu