←back to thread

Sampling with SQL

(blog.moertel.com)
175 points thunderbong | 1 comments | 20 Oct 24 10:58 UTC | HN request time: 0s | source
Show context
emmelaich ◴[20 Oct 24 22:46 UTC] No.41899108[source]▶
Is there something in the SQL standard that says functions are guaranteed to executed more than once?

I swear that once I used something like random() and it was only executed once, making it useless for the task at hand. I had to use some trick to ensure it was executed for each row.

I may have used it in the `select` part. Dialect was Oracle's, from memory.

related: https://xkcd.com/221/

replies(5): >>41899141 #>>41899381 #>>41900027 #>>41901762 #>>41903922 #
1. yen223 ◴[20 Oct 24 23:28 UTC] No.41899381[source]▶
Postgres makes a distinction between IMMUTABLE, STABLE, and VOLATILE functions, with volatile functions being functions that - with the same arguments - can produce different results even within the same statement. Therefore VOLATILE functions will always be executed once per call.

I'm not sure if this is part of the ANSI SQL standard.