Named parameters in database functions

[Lorenzo Bolla]

Cross posting from SO: http://stackoverflow.com/questions/15293547/named-parameters-in-database-functions-with-sqlalchemy

I have a function in my database (Postgres) that looks like this:

create function test_f(a text default '*', b text default '+') returns text as $$
    select a || ' ' || b;
$$ language sql;

Postgres allows calling it with named parameters:

mytest=> select test_f('a', 'b');                                           
test_f                                                                      
--------                                                                    
a b                                                                         
(1 row)                                                                     

mytest=> select test_f('a');                                                
test_f                                                                      
--------                                                                    
a +                                                                         
(1 row)                                                                     

mytest=> select test_f(b:='a');                                             
test_f                                                                      
--------                                                                    
* a                                                                         
(1 row)                                                                     

I want to do the same from Python, using SQLAlchemy's func construct, but it seems thatfunc does not honor named parameters:

In [85]: print(sqlalchemy.func.test_f('a', 'b'))                            
test_f(:test_f_1, :test_f_2)                                                

In [86]: print(sqlalchemy.func.test_f('a'))                                 
test_f(:test_f_1)                                                           

In [87]: print(sqlalchemy.func.test_f(a='a'))                               
test_f()                                                                    

Am I missing something, or func does not support named parameters?

[Michael Bayer]

On Mar 8, 2013, at 11:35 AM, Lorenzo Bolla <lbo...@gmail.com> wrote:

I want to do the same from Python, using SQLAlchemy's func construct, but it seems thatfunc does not honor named parameters:

In [85]: print(sqlalchemy.func.test_f('a', 'b'))                            
test_f(:test_f_1, :test_f_2)                                                

In [86]: print(sqlalchemy.func.test_f('a'))                                 
test_f(:test_f_1)                                                           

In [87]: print(sqlalchemy.func.test_f(a='a'))                               
test_f()                                                                    

Am I missing something, or func does not support named parameters?

it does not at the moment, PG's "named" parameter feature is pretty unusual and not something I've seen on any other database.   It's a feature that could be added, or certainly you could write a custom SQL construct for now that does it as well, see http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html