I'm looking to set up some Postgres/PostGIS migrations with clojure/jdbc.

running side-effect functions with SELECT is proving to be an issue, with most migration libs eventually throwing the A result was returned when none was expected error, because at some point they use clojure.java.jdbc/execute! or clojure.java.jdbc/db-do-commands, which seems understandable, but frustrating when you need to call a function that's critical to the migration.

The PostGIS's docs encourage using SELECT statement to create a spatial column:

SELECT AddGeometryColumn('parks', 'park_geom', 128, 'MULTIPOLYGON', 2 );

Has anyone run into this or found an appropriate workaround for using functions in a clojure/jdbc and Postgres migration?

related tidbits:

  • this description for manually registering a spatial column looks promising but seems remarkably heavy-handed for something that already has a supporting function
  • there's also PL/PgSQL's PERFORM statement that I stumbled across but it seems like I'm grasping at straws at that point, despite it looking promising
  • clojure.java.jdbc/execute! docs give a specific heads-up about only using "general (non-select) SQL operation[s]"
  • That seems a bug with the clojure tool. I guess it runs all migration statements using Statement.executeUpdate() rather than Statement.execute(). Can you wrap the call into a do block and use perform inside the do block? But this should rather be fixed in clojure – a_horse_with_no_name Dec 18 '15 at 9:04
  • I tried this: (execute! db-spec ["DO $$ PERFORM AddGeometryColumn('parks', 'park_geom', 128, 'MULTIPOLYGON', 2 ); $$;"]) and got a syntax error at or near "PERFORM", though I'm not a postgres aficionado, so perhaps there's a glaring error there for someone more experienced. Thanks! – user3276552 Dec 19 '15 at 1:44

You can avoid this by using the DO command like so:

DO $$ 
    PERFORM AddGeometryColumn('parks', 'park_geom', 128, 'MULTIPOLYGON', 2 );

Note the BEGIN and END are necessary as it has to be a valid plpgsql block.

| improve this answer | |

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.