cant use ad-hoc functions or clauses as values with updates or inserts

[Mike Bayer]

we have a notion of a PassiveDefault which defines a "default" value on a table column, which can be a SQL function. As such, this function gets fired off upon insert, implicitly by the database, without an external pre-execution of that value.

however, SQL supports the embedding of functions as values in INSERT or UPDATE statements for the VALUES or SET clauses. SQLAlchemy currently cant really get a "func" or "text()" object directly into the VALUES of an insert (unless you write the whole insert by hand, of course), and it seems to work with UPDATE but puts some nasty parenthesis in there for no good reason (SQLite in particular really despises unnecessary parenthesis). lets get full blown support for whatever you want to put into an individual VALUES or SET clause (note this is a separate idea from INSERT from a SELECT, which doenst have a VALUES clause).

[Mike Bayer]

added same logic used for updates into inserts, plus a unit test - changeset:1169

[Former user]

This works as long as you don't use this technique for populating Primary Key columns. I guess that SA finds that ID is None and it pregenerates it and that also tries to use func/text.

data = [= sqlalchemy.func.cc_uvoz_tekst_seq.nextval()
for i,tekst in enumerate(lines):
    data.append(dict(id=seq, uvoz_id=uvoz.id, pozicija=i+1, tekst=tekst))
isoz.TekstUvoza.table.insert().execute(*data)

You get:

[2006-03-19 10:33:03,158](]
seq) [engine](engine): SELECT cc_uvoz_tekst_seq.nextval
FROM DUAL
[10:33:03,158](2006-03-19) [engine](engine): {}
...
[10:33:03,178](2006-03-19) [engine](engine): SELECT cc_uvoz_tekst_seq.nextval
FROM DUAL
[10:33:03,178](2006-03-19) [engine](engine): {}
[10:33:03,229](2006-03-19) [engine](engine): INSERT INTO cc_uvoz_tekst (id, pozicija, tekst, uvoz_id) VALUES (cc_uvoz_tekst_seq.nextval, :pozicija,
:tekst, :uvoz_id)
[10:33:03,229](2006-03-19) [engine](engine): [{'tekst': '...', 'uvoz_id': 61, 'pozicija': 1, 'id': 5081}, {'tekst': '...', 'uvoz_id': 61, 'pozicija': 2, 'id': 5082}, ....

[Mike Bayer]

did I say something here ? :) oh well, nevermind. yah this is a bug because when it inserts/updates it only looks at bind parameters to see if a value was set, but not at overall SET/VALUES parameters. INSERT/UPDATE will have to add a new list to the compiled object called parameter_is_set or something like that which the engine will query, instead of checking bind parameters alone, before deciding if it needs to use the Table's built-in defaults.

[Former user]

I don't get why the extra list is necessary, guarding param[c.name](c.name)=newid with if c.name in compiled.binds in SQLEngine._process_defaults is enough for me. With this change, against revision:1355 all tests pass and the repro_compiled_insertcase.py thing I attatched to ticket:169 behaves as I'd expect. There is a cache lookup based on statement & parameters that causes a regeneration if a litteral / db func clash happens isn't there ?

[Former user]

treat compiled.binds as truth wrt non default param values

[Former user]

Oh, gronk, it was that None place holder thing that caused all the trouble: ticket:169, gah! :-)

[Mike Bayer]

also, it might be interesting to have a flag on PassiveDefault "inline=True" which says, "this default is not part of the CREATE TABLE but is rather to be assembled into the generated INSERT or UPDATE clause". that way we could again have PassiveDefault as an on_update too.

[Former user]

When I found out about this limitation it was a real set-back as I now have to define a lot of my tables in plain PostgreSQL SQL instead, to get the ON UPDATE CURRENT_TIMESTAMP() behavior, which is important as I have other non-SA software also doing updates.

Just wanted to wave my hand over here to let you know that it's a much desired functionality :)

Thanks for a great piece of software.

Aron

[Mike Bayer]

the "ON UPDATE" schema-level keyword is supported, its part of ForeignKeyConstraint, if thats what you were looking for.

if you are just looking to fire off a function as part of the SET clause of an update statement, you can just execute the function separately and use its return value.

what exactly is the functionality here that you totally cannot do without the function call "inlined" into the update statement ? are you correlating rows from a select into the function ?

[Former user]

the "ON UPDATE" schema-level keyword is supported, its part of ForeignKeyConstraint?, if thats what you were looking for.

Ah, no I was referring to the "ON UPDATE" for regular non-foreign-key columns 1. E.g:

But I seem to have jumped the gun here as this piece of syntactic sugar seems to be a MySQL feature and not available in PostgreSQL (or any other DBMS?), where an explicit separately defined trigger is necessary instead.

  if you are just looking to fire off a function as part of the SET clause of an update statement, you can just execute the function separately and use its return value.

Yes, of course. It would just be more convenient to not have to worry about it and leave the timestamping to the database system. And also a little safer against programmer error. Also, if the client application is in different time zone it would have to take care to do time zone conversion (if timestamps are supposed to be in server time zone), which is another possible point of error that could be avoided if the timestamping were done transparently on the database server.

  what exactly is the functionality here that you totally *cannot* do without the function call "inlined" into the update statement ? are you correlating rows from a select into the function ?

Nothing I cannot do at all. I didn't say so and I definately didn't mean for it to sound as if I was complaining, just found the ticket and thought I'd chime in :)

I was in the wrong when I thought PostgreSQL had this capability, and since it requires an explicit trigger in the database to get the behavior I now see it's not a task for SA at all. I was to eager.

''Best regards''[[BR]([BR)]
''Aron Stansvik''

[Former user]

Forgot foot note in last comment. The link shows MySQL's "ON UPDATE" magic.

1 http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html

[Mike Bayer]

no problem, i had been confused about ON UPDATE as well in the beginning due to mysql having it, but postgres not (but seeing it in the postgres docs attached to a CONSTRAINT). so yeah thats why we dont have the ON UPDATE built in to columns although we could conceivably create mysql-specific functionality to do this (like a MySQLOnUpdate() or something). i tend to not get into large sets of db-specific functionality (like triggers, pl/SQL etc) just because i cant maintain all that code, but theres no reason others cant contribute.

[Former user]

Ah now I see, I mixed it up as well. So there is a way to do it inside column spec in PostgreSQL after all, that's good to know as I will probably use the feature in my SQL scripts anyway. I'm trying the Migrate package out and so far it's working out pretty fine, and has support for plain SQL scripts.

Not dabbling with engine specific functionality sounds very sane.

''..but theres no reason others cant contribute.''

I'm very green on Python I'm afraid, maybe in the future I can give something back. For now I really need to get this prototyped in time :/

Thanks!

''Aron''

[Former user]

Sorry, I was doubly confused. It is not possible in PostgreSQL after all. The ON UPDATE in the column spec is part of the in-column-spec foreign key definition, not something that can be applied to any column. Triggers it is! ;)

''Aron''

[Mike Bayer]

the main idea of this ticket, that you can stick SQL expressions into the values/set params of UPDATE/INSERT and they wont get clobbered by column-level defaults, is implemented in changeset:2113.

[Mike Bayer]

Removing milestone: 0.4.0 (automated comment)