4

I am writing an application that uses postgres. Each microservice uses its own schema inside the database.

So connect to the database schema I use this jdbc string:

jdbc:postgresql://${db.host}/${db.name}?currentSchema=${schema}

This kind of works, so each sql statement can use the tables inside the schema without the danger of accidentally accessing the other services schema.

But in one schema I'd like to use the earthdistance extension. I only managed to install it without setting a schema and when I connect to the DB without the currentSchema parameter I can access the <@> operator to calculate the distance. But with the parameter postgres complains that it's not able to find the operator.

Is there a way to access the operator that is in the public namespace, when I connected with the currentSchema parameter?

I'm kinda lost here :-(

  • Can you do something like SET search_path TO ${schema},public; ? – Clément Prévost May 30 '15 at 17:39
  • Hmm, I don't think so. I haven't found something to do that properly. I could set the search path on a per user basis, but right now all services use the same user and just stick to their schema with the currentSchema parameter. I think I just switch the approach and use a dedicated db instead of schemas in a shared db... – Patrick Cornelissen May 31 '15 at 9:49
  • You should set the search_path on a per connection basis. If it can't find the operator, that's because it's not in your search_path. I couldn't test this on my own as ?currentSchema= seems to be ignored with my version of odbc but you can use the SQL command SHOW search_path;. If the extension schema (ie. public) isn't there, you won't be able to use the <@> operator. – Clément Prévost May 31 '15 at 11:41
6

You could schema qualify the operators when you invoke them.

For example, instead of writing

SELECT current <@> destination

You can write:

SELECT current operator(public.<@>) destination

See Operator Invocations for more information

  • Ah, I tried to find how to use the schema in the operator call. Thanks! – Patrick Cornelissen Jun 1 '15 at 17:06

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.