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.
