3 ms·
Ok, my SQL skills might be a bit lacking, but from the article: > select audit.enable_tracking('public.members'); So you can actually have functions that have
by SCLeo 5y ago
Ok, my SQL skills might be a bit lacking, but from the article:
> select audit.enable_tracking('public.members');
So you can actually have functions that have side effects in a select statement? I guess nothing prevents it, so it is allowed. But somehow I have the impression that select statements don't change anything.
- znep 5y agoFor even more fun, try "SELECT pg_cancel_backend(pid) from pg_stat_activity". (DON'T ACTUALLY DO THIS on anything other than a personal test db as it will kill all the connections it has permission to kill) Related, postgres has a number of different volatility options for functions so you can declare if there are side effects: https://www.postgresql.org/docs/14/xfunc-volatility.html https://www.postgresql.org/docs/14/xfunc-volatility.html These can become very important in some cases to let the optimizer have the freedom to shine.
- giraffe_lady 5y agoYep you can, though a lot of systems that should know better also assume they don't have side effects. For example postgrest GETs happen in readonly transactions which would cause a problem if you wanted to use a system like this to keep an audit logs of hits to a certain endpoint or whatever. I think you can use a standalone VALUES statement to avoid side effects? Those aren't well supported even though it's part of the SQL spec, but postgres does have them.