patternsqlMinor
Access operator added by extension inside a schema
Viewed 0 times
operatoraddedextensionschemaaccessinside
Problem
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:
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 :-(
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 :-(
Solution
You could schema qualify the operators when you invoke them.
For example, instead of writing
You can write:
See Operator Invocations for more information
For example, instead of writing
SELECT current destinationYou can write:
SELECT current operator(public.) destinationSee Operator Invocations for more information
Code Snippets
SELECT current <@> destinationSELECT current operator(public.<@>) destinationContext
StackExchange Database Administrators Q#102852, answer score: 6
Revisions (0)
No revisions yet.