On Sat, May 16, 2020, at 1:04 PM, Erol Merdanović wrote:
> Hi
>
> I have a model definition
>
> class Product(db.Model):
> __tablename__ = "products"
>
> id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
> sku = db.Column(db.String(255), nullable=False, unique=True, index=True)
>
>
> Product.base_price = column_property(
> cast(func.get_product_price(), Numeric(15, 5)).label("base_price")
> )
>
> get_product_price is postgres function
>
>> CREATE OR REPLACE FUNCTION public.get_product_price()
>> RETURNS numeric AS
>> $BODY$
>> BEGIN
>> return 100;
>> END;
>> $BODY$
>> LANGUAGE plpgsql VOLATILE
>> COST 100;
>
> If I execute the query as
>
> Product.query.order_by(Product.base_price.asc()).all()
>
> it all works great.
>
> The problem is if I want to pass the product to function
>
>> CREATE OR REPLACE FUNCTION public.get_product_price(product products)
>> RETURNS numeric AS
>> $BODY$
>> -- do something with product
>> BEGIN
>> return 100;
>> END;
>> $BODY$
>> LANGUAGE plpgsql VOLATILE
>> COST 100;
>
> Is it possible to pass the reference/alias to product to function? I tried
> with alias but without any success.
I'm not deeply familiar with PGs elaborate syntaxes, are you trying to pass
whole table rows into the function? We're only beginning to consider supporting
this syntax which you can see in issues like
https://github.com/sqlalchemy/sqlalchemy/issues/3566
https://github.com/sqlalchemy/sqlalchemy/issues/5256 .
Assuming SQLAlchemy supported that syntax, which you *can* get using workaround
in the above linked issues, it's still not clear how this would apply to a
mapped column_property(). A column_property() is a fixed SQL expression that
does not change and does not accept arguments. If you wanted to pass runtime
arguments to a mapped attribute and have it return a SQL function, use a hybrid
method:
https://docs.sqlalchemy.org/en/13/orm/extensions/hybrid.html#sqlalchemy.ext.hybrid.hybrid_method
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/071cec63-88d8-4660-bfe0-e5317924493e%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/071cec63-88d8-4660-bfe0-e5317924493e%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/efaa2813-221e-4520-8712-cb9c23a36ec3%40www.fastmail.com.
