Create Postgres function in Python

Question

I am creating trying to create a function inside Postgres from Python. My connection to postgres database uses Psycopg 2 and connects successfully in other instances. Code:

pg_cursor.execute('CREATE OR REPLACE FUNCTION %s.fix_geometry(geometry) \
    RETURNS SETOF geometry AS\
    $BODY$\
        SELECT geom the_geom\
        FROM \
                (\
                SELECT (st_dump(st_buffer(st_snaptogrid(st_makevalid(the_geom), 0.5), 0))).geom\
                FROM (SELECT geom the_geom FROM st_dump(st_snaptogrid($1, 0.5))) a\
                ) b\
        WHERE geometrytype(geom) = \'POLYGON\' AND st_area(geom) >= 0.01;\

    $BODY$\
       LANGUAGE sql VOLATILE\
       COST 100\
       ROWS 1000;' %(schema)) #line 84

pg_connection.commit()
pg_cursor.execute('ALTER FUNCTION %s.fix_geometry(geometry) OWNER TO analysis' % (schema))
pg_connection.commit()

I get an error:

line 84, in
ROWS 1000;' %(schema))
ProgrammingError: type geometry does not exist

When I run the code in pgAdmin it executes successfully. What am I missing?

Python 2.7, Postgres 9.3

D_C · Accepted Answer · 2017-01-12 21:14:39Z

1

It turns out that the error was just outside of the provided code snippet. Postgre has the geometry type inside of the public schema and when I defined the search path for this code, I only defined the schema in which I was working; public was not included. So....

pg_cursor.execute('set search_path =  %s, public' % (schema))
pg_connection.commit()

answered Jan 12, 2017 at 21:14

D_C

4105 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create Postgres function in Python

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related