We've been using plpython stored procedures for postgres recently and, after seeing a post about someone else using them, I thought I'd put a very rough script up that I use to help in their development. One of the problems with plpython is the quoting business of having to include python in a quoted string. My simple solution is to just do a little fiddling with a python function to create a plpython function. The only constraint is that the second line has to be a single line comment with the function signature on it. e.g.

def insert_node_under():
    # ( TEXT, LTREE, TEXT, TEXT, TEXT ) RETURNS INT4
    table, parent, pathsegment, textid, label = args
    # Use plpy.notice to debug
    # e.g. plpy.notice(args[0])
    sql = plpy.prepare("SELECT max(ord) FROM %s WHERE
                            path ~ $1" %
                            table,['lquery'])
    out = plpy.execute(sql,['%s.*{1}' % parent])
    ordmax = out[0]['max']
    # Make sure that we have the next highest number
    if ordmax:
        newOrd = ordmax + 1
    else:
        newOrd = 1
    # Add the path
    path = '.'.join([parent,pathsegment])
    # note that types need to use raw postgres types and
    # substitutions are dollar variables
    sql = plpy.prepare("INSERT INTO %s (path,textid,label,ord)
                         VALUES ($1, $2, $3,
                         $4)" %
                            table,['ltree','text','text','int4'])
    out = plpy.execute(sql,[path,textid,label,newOrd])
    # Retrieve the new node's id.
    stmt = plpy.prepare("SELECT id from %s WHERE path = $1"
             % table,
                                           ['ltree'])
    id = plpy.execute(stmt,[path])[0]['id']
    return id

My main reason for using this is so that I can forget about quoting and I also get syntax highlighting.

After reading the spyced post, I realised I should probably add function arguments but I don't see a way of avoiding the type signature comment. I had thought about using the python parser to allow a little more intelligent parsing, but it works for what I need at the moment.

Here's a link py2ply.py