Yeah everything is working in psql just fine.
select current_schema();
current_schema
----------------
public
(1 row)
Time: 1.226 ms
show search_path;
search_path
-----------------
"$user", public
(1 row)
select add(2,4);
add
-----
6
(1 row)
But you jogged my memory and think I found the problem. I dug back through
my code and found this buried in it
def clearSearchPathCallback(dbapi_con, connection_record):
'''
When creating relationships across schema, SQLAlchemy
has problems when you explicitly declare the schema in
ModelClasses and it is found in search_path.
The solution is to set the search_path to "$user" for
the life of any connection to the database. Since there
is no (or shouldn't be!) schema with the same name
as the user, this effectively makes it blank.
This callback function is called for every database connection.
For the full details of this issue, see:
http:
//groups.google.com/group/sqlalchemy/browse_thread/thread/88b5cc5c12246220
dbapi_con - type: psycopg2._psycopg.connection
connection_record - type: sqlalchemy.pool._ConnectionRecord
'''
cursor = dbapi_con.cursor()
cursor.execute('SET search_path TO "$user",functions')
dbapi_con.commit()
listen(sqlalchemy.pool.Pool, 'connect', clearSearchPathCallback)
which shows my setting the search_path to exclude public. Changing that
line 'SET search_path TO "$user",functions, public' fixes everything SQLA.
It's been a while since I've looked at my code that sets up the
DatabaseConnection. I must have had an original reason to exclude public
but it doesn't seem relevant anymore. Thanks for your help.
On Wednesday, August 1, 2018 at 2:18:55 PM UTC-4, Brian Cherinka wrote:
>
> Hi,
>
> What's the best way to access functions that live in schema='public' in
> the postgres databases? Some postgresql extensions install functions in
> the public schema that I would like accessible via *sqlachemy.func,* however
> I get an error when attempting to call them. Am I missing something when
> setting up my Base Classes? Maybe regarding the `search_path`? I'm
> defining my models and tables with a Declarative Base. My
> default_schema_name is set to 'public'. I've read through this page,
> http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path
>
> <http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path>but
>
> it's not clear where I should be setting my search_path to ensure it
> includes the public schema.
>
> As an example, I have a test "add" function in the 'public' schema , which
> crashes presumably because it cannot find the function definition
>
> session.query(func.add(2,4)).all()
>
> ProgrammingError: (psycopg2.ProgrammingError) function add(integer,
> integer) does not exist
> LINE 1: SELECT add(2, 4) AS add_1
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
> [SQL: 'SELECT add(%(add_2)s, %(add_3)s) AS add_1'] [parameters: {'add_3':
> 4, 'add_2': 2}]
>
>
> and a test "newadd" function defined in an explicit schema called
> "functions". This seems to automatically get reflected and mapped. And
> works perfectly.
>
> session.query(func.newadd(2,4)).all()
>
> [(6)]
>
> One solution is to install the postgres extension into the functions
> schema, but this kind of breaks the usage within postgres itself. I have
> to always explicity set search_path='functions' in order to use them. So
> it's not ideal. Ideally, I'd like *sqlachemy.func* to understand
> functions that live either in the "functions" or "public" schema. Any
> ideas on how to fix this?
>
> Cheers, Brian
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
