Re: 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(at)mrna(dot)tn(dot)nic(dot)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(at)gmail(dot)com>

KM,

>
> 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(at)mrna(dot)tn(dot)nic(dot)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