postgresql: example use of python procedural language

plpythonu.sql

-- query with stored plan
CREATE or replace FUNCTION pybench1(id int) RETURNS text AS '    
    if (SD.has_key("plan")):
        plan = SD["plan"]
    else:
        plan = plpy.prepare("SELECT * FROM pagetimer pt, pagebrowser pb WHERE pt.idtimer = $1 and pt.idtimer = pb.idtimer", ["int4"])
        SD["plan"] = plan

    rec = plpy.execute(plan, [id])
    if (rec.nrows() > 0):
        return rec[0]["parent"]
    else:
        return "b.d."
' LANGUAGE plpythonu SECURITY DEFINER;

-- simple query
CREATE or replace FUNCTION pybench2(id int) RETURNS text AS '    
    rec = plpy.execute("SELECT * FROM pagetimer pt, pagebrowser pb WHERE pt.idtimer = "+str(id)+" and pt.idtimer = pb.idtimer")
    if (rec.nrows() > 0):
        return rec[0]["parent"]
    else:
        return "b.d."
' LANGUAGE plpythonu SECURITY DEFINER;

-- private dictionary (for function calls in one session)
CREATE or replace FUNCTION testpy() RETURNS int AS '    
    if SD.has_key("tmp"):
        SD["tmp"] = SD["tmp"] + 1
    else:
        SD["tmp"] = 1

    return SD["tmp"]

' LANGUAGE plpythonu SECURITY DEFINER;

-- global dictionary (for all functions in one session)
CREATE or replace FUNCTION testpy2() RETURNS int AS '    
    if GD.has_key("tmp"):
        GD["tmp"] = GD["tmp"] + 1
    else:
        GD["tmp"] = 1

    return GD["tmp"]

' LANGUAGE plpythonu SECURITY DEFINER;

-- logging
CREATE or replace FUNCTION testpy3() returns int AS '    
    plpy.debug("test debug")
    plpy.log("test log")
    plpy.info("test info")
    plpy.notice("test notice")
    plpy.warning("test warning")

    return 1
' LANGUAGE plpythonu SECURITY DEFINER;

-- function with variable parameters list
CREATE or replace FUNCTION testpy4(keys text, vals text) returns int AS '
    params = dict(zip(keys.split(","), vals.split(",")))
    for p in params.keys():
        plpy.info(params[p])

    return 1
' LANGUAGE plpythonu SECURITY DEFINER;

select testpy4('p1,p2,p3', 'v1,v''2'',?v3&ee=2&ss')