Thread: plpython
Hi all, Can someone hint on resources for using plpython for writing stored procedures ? I have gone thru official docs for 8.1.5 for plpythonu but its not in detail/with examples. When is plpython going to be considered safe ? any targeted version ? regards, KM
km <km@mrna.tn.nic.in> writes:
> Can someone hint on resources for using plpython for writing stored
> procedures ?
Sure. But it depends a lot on what you're willing to do. The docs have the
details. This one I did just to learn it:
CREATE FUNCTION f_v_fechamento(p_cliente_id integer, p_data date, p_pago boolean, OUT retorno record) RETURNS record
AS $$
p_cliente_id = args[0]
p_data = args[1]
p_pago = args[2]
w_total = 0
w_amostra = 0
w_final_do_mes = plpy.execute("SELECT f_v_final_do_mes(%s::date)" % p_data)
w_inicio_do_mes = plpy.execute("SELECT f_v_inicio_mes(%s::date)" % p_data)
retorno = dict()
retorno['w_inicio_do_mes'] = w_inicio_do_mes
retorno['w_final_do_mes'] = w_final_do_mes
return retorno
$$
LANGUAGE plpythonu STABLE;
It can be rewritten in a better way but was the handier example I had here
that had queries and used a bit more of PostgreSQL 8.1 :-)
> I have gone thru official docs for 8.1.5 for plpythonu but its not in
> detail/with examples.
It's just pure Python. And you use plpy for querying the database.
> When is plpython going to be considered safe ? any targeted version ?
I hope never, otherwise we loose some facilities. You understood the concept
of 'safe' and 'unsafe' in PostgreSQL's docs, right? It might not be what
you're thinking.
Be seeing you,
--
Jorge Godoy <jgodoy@gmail.com>
KM,
CREATE OR REPLACE FUNCTION myfunc(text)
RETURNS text AS
$BODY$
eingabe=args[0]
hello = "Good Morning %s" % (eingabe,)
return hello
$BODY$
LANGUAGE 'plpythonu' VOLATILE;
ALTER FUNCTION myfunc(text) OWNER TO postgres;
that as a first example to get you started. The other one I could give you is using Pyro, which brings in rather uncommon challenges.
Hey, you fell into the same "safe" and "unsafe" trap than me!
"unsafe" does not have the information: "it is possibly errorprone to use this language"
"unsafe" simply says: "it is impossible for the database to guarantee, that a bad minded programmer can do harmfull thins with this language"
For example: with plpython you can read and delete files on the server. There is no way for PostgreSQL to stop plpython from doing harm; so it is considered "unsafe".
(Btw: there is no restricted mode of execution for Python, google the Python Mailingslists for it; "sandbox" is a helpfull keyword)
So: plpython is only recommended to be used by trustworthy programmers. Not by the general public.
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.
Can someone hint on resources for using plpython for writing stored procedures ?
I have gone thru official docs for 8.1.5 for plpythonu but its not in detail/with examples.
CREATE OR REPLACE FUNCTION myfunc(text)
RETURNS text AS
$BODY$
eingabe=args[0]
hello = "Good Morning %s" % (eingabe,)
return hello
$BODY$
LANGUAGE 'plpythonu' VOLATILE;
ALTER FUNCTION myfunc(text) OWNER TO postgres;
that as a first example to get you started. The other one I could give you is using Pyro, which brings in rather uncommon challenges.
>When is plpython going to be considered safe ? any targeted version ?
Hey, you fell into the same "safe" and "unsafe" trap than me!
"unsafe" does not have the information: "it is possibly errorprone to use this language"
"unsafe" simply says: "it is impossible for the database to guarantee, that a bad minded programmer can do harmfull thins with this language"
For example: with plpython you can read and delete files on the server. There is no way for PostgreSQL to stop plpython from doing harm; so it is considered "unsafe".
(Btw: there is no restricted mode of execution for Python, google the Python Mailingslists for it; "sandbox" is a helpfull keyword)
So: plpython is only recommended to be used by trustworthy programmers. Not by the general public.
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.
> Sure. But it depends a lot on what you're willing to do. The docs have the
> details. This one I did just to learn it:
>
> CREATE FUNCTION f_v_fechamento(p_cliente_id integer, p_data date, p_pago boolean, OUT retorno record) RETURNS record
> AS $$
> p_cliente_id = args[0]
> p_data = args[1]
> p_pago = args[2]
>
> w_total = 0
> w_amostra = 0
> w_final_do_mes = plpy.execute("SELECT f_v_final_do_mes(%s::date)" % p_data)
> w_inicio_do_mes = plpy.execute("SELECT f_v_inicio_mes(%s::date)" % p_data)
>
> retorno = dict()
> retorno['w_inicio_do_mes'] = w_inicio_do_mes
> retorno['w_final_do_mes'] = w_final_do_mes
>
> return retorno
> $$
> LANGUAGE plpythonu STABLE;
>
>
> It can be rewritten in a better way but was the handier example I had here
> that had queries and used a bit more of PostgreSQL 8.1 :-)
>
Thanks for that snippet. why is that 'STABLE' at the end of the function ?
i am stuck at createlang for plpythonu! with postgres user
error reads:
$createlang plpythonu template1;
createlang: language installation failed: ERROR: could not load library "/usr/local/pgsql/lib/plpython.so":
/usr/local/pgsql/lib/plpython.so:undefined symbol: Py_InitModule4_64
i am on a x86_64 linux box. couldnt comprehend the error.
plpython.so is in /usr/local/pgsql/lib
whats wrong?
regards,
KM
2006/10/27, km <km@mrna.tn.nic.in>: > i am stuck at createlang for plpythonu! with postgres user > error reads: > > $createlang plpythonu template1; > createlang: language installation failed: ERROR: could not load library "/usr/local/pgsql/lib/plpython.so": /usr/local/pgsql/lib/plpython.so:undefined symbol: Py_InitModule4_64 > > i am on a x86_64 linux box. couldnt comprehend the error. > plpython.so is in /usr/local/pgsql/lib > > whats wrong? Read the thread named plpython.so Regards, Clodoaldo Pinto Neto