"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".
Consider:
foo=# \d artists
Table "public.artists"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
name | text | | |
Indexes:
"artists_pkey" PRIMARY KEY, btree (id)
foo=# select a.name, name(a) from artists as a;
name | name
---------------+---------------
peter gabriel | peter gabriel
(1 row)
[1] https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE
> In general, whenever a column has a server side default of this
> nature, you want to use FetchedValue or some subclass of this in the
> column definition, so the ORM as well as Core knows that something in
> the server will be generating a value for this column:
> https://docs.sqlalchemy.org/en/20/core/defaults.html#triggered-columns
> /
> https://docs.sqlalchemy.org/en/20/orm/persistence_techniques.html#orm-server-defaults
>
>>
>> from sqlalchemy import Computed
>>
>> something = Table("something", metadata,
>> Column("id", Integer, primary_key=True),
>> Column("name", Text),
>> Column("counter", Integer, Computed("counter")))
>>
>> but accordingly to the documentation [2] "SA behavior ... is currently
>> that the value [assigned to the column] will be ignored", so that does
>> not bring me any advantage.
>
> 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"
instance_of_something.counter = 42
session.flush()
I was just imagining something that could raise an error earlier.
> true "readonly" at the Core level, where any attempt to use
> connection.execute() in such a way that data is being sent to this
> column, you'd need to use event hooks such as before_cursor_execute()
> https://docs.sqlalchemy.org/en/20/core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute
> and then do heuristics to figure out what the statement is, and if any
> of the parameters point to this column. I would not go this route
> since it's overkill and not that reliable for said overkill.
Right, not worth the effort indeed.
> If you want to absolutely disallow client side changes to this column,
> since you are setting up PG defaults anyway, using a trigger or a true
> COMPUTED column would be the easiest route.
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.
[2] https://www.postgresql.org/docs/15/ddl-generated-columns.html
> 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?
Anyway, as said, it was just to be sure I didn't miss some SA magic (it
wouldn't be the first time you've managed to surprise me, and I bet
you'll keep doing that :-). My coworkers will surely be able to cope
with an error coming back from the database at flush time!
Thanks&bye, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it | -- Fortunato Depero, 1929.
--
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/878reuthgi.fsf%40metapensiero.it.
