On Fri, Apr 14, 2023, at 3:02 PM, Lele Gaifax wrote:
> "Mike Bayer" <mike_not_on_goo...@zzzcomputing.com> writes:
>
>> On Fri, Apr 14, 2023, at 8:03 AM, Lele Gaifax wrote:
>>> I now have
>>>
>>> CREATE TABLE something (id SERIAL, name TEXT)
>>>
>>> CREATE FUNCTION counter(something)
>>> RETURNS INTEGER AS $$
>>> SELECT count(*) FROM something_else se
>>> WHERE se.something_id = $1.id
>>> $$ STABLE SQL
>>>
>>> and thus existing queries such as
>>>
>>> SELECT s.name, s.counter
>>> FROM something AS s
>>>
>>> work as before.
>>
>> how does "SELECT s.counter" work if the table does not have an actual
>> "counter" column?
>
> As explained here [1], "the field notation and functional notation are
> equivalent", that is "SELECT a.foo FROM t AS a" and "SELECT foo(a) FROM
> t AS a" means the same, when "foo" is a function accepting the "implicit
> composite type corresponding to a row in the table t".
oh geez it's that silly PostgreSQL syntax. not a fan. you can use that but
you'd be on your own....
>
>>
>> Using Computed in this way is equivalent to using FetchedValue. Why is
>> that a problem?
>
> No, it's not a problem, but the following
>
> stmt = something.update().values(counter=42).where(something.c.id=1)
> connection.execute(stmt)
>
> will raise an error at execution time, as will, assuming
> "instance_of_something" is an instance of the ORM class mapped to the
> table "something"
So what then is a "read only column" if not one that raises an error if someone
tries to write to it explicitly?
> instance_of_something.counter = 42
> session.flush()
>
> I was just imagining something that could raise an error earlier.
well you have the ORM validates and the before_cursor_execute approaches, but I
would think if this is simple developer level programming guards, the PG
exception is perfect
>
> Unfortunately in this case PG does not help: the closest thing is a
> "GENERATED" column [2], but (up to version 15 at least) it "is a special
> column that is always computed from other columns", it cannot be an
> arbitrary subquery.
trigger, then. im sure this is a thing PG can do
>
>> Without using server-side constructs, in a practical sense, simply
>> omitting the column from the Table or using mapper.exclude_properties
>> is in my experience sufficient.
>
> Uhm, how could I then select that value, to be returned by an API that
> queries the "something" table?
OK so the "readonly" you are looking for includes one where your application
actually needs to load it, then fine, dont exclude it from your mappings.
--
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/eb9fc1e0-8494-4ec8-aad5-4cab3ae10bf5%40app.fastmail.com.
