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()
this is the problem, you can't pass a declarative class or Table object
as an argument to a function. It is extremely unusual that Postgresql
allows the name of a table or alias to be an argument to a function, so
to support this you need to hardwire the table or alias name in use to
make this happen as a SQL expression, as in:
print(
session.query(Cube).filter(
func.specres_sum_segment(literal_column("cube"), 2000, 2005) ==
12000
).all()
)
but I get the error
ProgrammingError: (psycopg2.ProgrammingError) can't adapt type
'DeclarativeMeta'
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.
