Postgresql – Using SELECT to call a function in a jdbc migration

clojurejdbcpostgispostgresql

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]"

Best Answer

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.

1. Subquery expression

You can fix it with parentheses like @a_horse commented:

SELECT * FROM test_function((SELECT customerid FROM tableX where id = 1));

But this form is rather error-prone. Nothing in the code guarantees that the sub-select only returns a single row. We don't know whether id is unique and neither does Postgres (unless it looks up system catalogs). We have to rely on it and hope it won't break.

We could add LIMIT 1 to be sure, but then we probably should also add ORDER BY to get deterministic results ...

2. `JOIN LATERAL`

The modern syntax for this (Postgres 9.3+) would be a LATERAL join:

SELECT f.* 
FROM   tableX t, test_function(t.customerid) f
WHERE  t.id = 1;

Which is short syntax for:

SELECT f.* 
FROM   tableX t
CROSS  JOIN LATERAL test_function(t.customerid) f
WHERE  t.id = 1;

Exactly the same result as above, except for one subtle but possibly important difference:

1. If tableX has no row for t.id = 1, the function is never called and the query returns nothing (no row). If, on the other hand, a row is found with customerid IS NULL, the function is called with NULL input and we get whatever the function returns for NULL.
  That's how it should be.
1. The form with the subquery expression cannot distinguish between these two cases. "No row" results in the same NULL value as customerid being NULL. The function is called with NULL input either way.
  That's not how it should be.

The LATERAL query also does not break if id is not unique. And you can easily return additional columns from tableX if you want.

Best Answer

Related Solutions

No such column error jdbc

PostgreSQL – Call Function with Subselect Statement Argument

1. Subquery expression

2. JOIN LATERAL

Related Question

2. `JOIN LATERAL`