Re: [sqlalchemy] accessing functions in the postgres public schema using sqlalchemy.func

[Mike Bayer]

On Wed, Aug 1, 2018 at 2:18 PM, 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
> but it's not clear where I should be setting my search_path to ensure it
> includes the public schema.

the search_path should automatically include the "public" schema by default.

>
> 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}]

if you log in via psql, can you run the "add()" function?   you need
to get things working there first.   check the search_path, etc.



>
>
>  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()

The terms "reflected" and "mapped" are both SQLAlchemy terms, and in
that regard, neither has anything to do with a SQL function.   Your
result would indicate that Postgresql's search path for functions
includes this "functions" schema.I'm not aware if this is some
kind of default behavior in Postgresql or something but again, using
psql by itself to see what's happening is the first thing to work
with.


>
> [(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.

If you are doing this, that would be why your "newadd" function in the
"functions" schema works...

> 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?

this is all stuff you should ask on stackoverflow.  I can't reproduce
your issue:

$ psql -U scott test
psql (10.4)
Type "help" for help.

test=# select current_schema();
 current_schema

 public
(1 row)

# create a function, goes into the "public" schema by default

test=# CREATE FUNCTION add(integer, integer) RETURNS integer
test-# AS 'select $1 + $2;'
test-# LANGUAGE SQL
test-# IMMUTABLE
test-# RETURNS NULL ON NULL INPUT;
CREATE FUNCTION

# works

test=# select add(1, 2);
 add
-
   3
(1 row)

test=# show search_path;
   search_path
-
 "$user", public
(1 row)

# now remove "public" from search path

test=# set search_path="%user";
SET

# function is gone
test=# select add(1, 2);
ERROR:  function add(integer, integer) does not exist
LINE 1: select add(1, 2);
   ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

# put it back
test=# set search_path="%user",public;
SET


# works again

test=# select add(1, 2);
 add
-
   3
(1 row)

test=#





>
> 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.

-- 
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] accessing functions in the postgres public schema using sqlalchemy.func

[Brian Cherinka]

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
  
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.