Re: [sqlalchemy] correct syntax to pass an sqlalchemy table class into postgresql functions?

[Mike Bayer]

On 5/20/15 12:09 PM, Brian Cherinka wrote:
Hi,

I've built a postgresql function that takes as input a row from a 
table, and returns the sum of a particular column (of type array) 
between two specified indices.  Here is how I've defined my function

    CREATE OR REPLACE FUNCTION specres_sum_segment(cube datadb.cube,
    index1 integer, index2 integer) RETURNS numeric
        LANGUAGE plpgsql STABLE
        AS $$

    DECLARE result numeric;
    BEGIN
    select sum(f) from unnest(cube.specres[index1:index2]) as f into
    result;
    return result;
    END; $$;

and here is how it works in psql.

    select c.pk from datadb.cube as c
    where(specres_sum_segment(c,2000,2005) < 12000);


This works and returns the cube entries where this condition is true. 
 Now I'm trying to call this function with an SQLalchemy query.  I've 
mapped a DeclarativeMeta class called Cube to my datadb.cube table, 
but when I try to run my session query I'm getting an error.

My sqlalchemy session query is

    
session.query(datadb.Cube).filter(func.specres_sum_segment(datadb.Cube,2000,2005)==
    12000).all()


but I get the error

    ProgrammingError: (psycopg2.ProgrammingError) can't adapt type
    'DeclarativeMeta'

sounds like your use of declarative is incorrect, please share the means 
by which the Cube class is declared as well as its base.





What is the right syntax to use when passing a mapped SQLalchemy class 
into a function so postgresql will understand it?  I'm using 
SQLalchemy 1.0.0 and PostgreSQL 9.3.  Any help would be appreciated. 
 Thanks.

Cheers, Brian

--
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 
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com 
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.