3

I am creating trying to create a function inside PostgreSQL from python. My connection to PostgreSQL database uses psycopg2 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 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?

I had originally put this question here: https://stackoverflow.com/q/41602531/4641482 but have yet to have had a response, maybe wrong forum?

Python 2.7, PostgreSQL 9.3

2

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()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.