HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Access operator added by extension inside a schema

Submitted by: @import:stackexchange-dba··
0
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:

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

SELECT current  destination


You can write:

SELECT current operator(public.) destination


See Operator Invocations for more information

Code Snippets

SELECT current <@> destination
SELECT current operator(public.<@>) destination

Context

StackExchange Database Administrators Q#102852, answer score: 6

Revisions (0)

No revisions yet.