5

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]"
user3276552
  • 151
  • 3
  • 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 09: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 01:44

1 Answers1

3

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

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

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

zeroimpl
  • 131
  • 3