Hi Sylvain,
Yes, Derby 10.5 introduced generated columns, so the syntax below will work:
create table foo
(
...
createdBy generated always as ( myGeneratorFunction() ),
...
);
As you note, this would solve part of Thomas' problem because a
generated column cannot be edited directly. You can change a generated
column only if it depends on other columns in the same row. In the
example above, the generated column doesn't depend on any other columns
in the row so the initial value can't be changed.
However, I don't think that generated columns will help Thomas. That is
because the generation expression must be deterministic. The
current_user expression is possibly non-deterministic, so the following
declaration is not allowed:
createdBy generated always as ( current_user )
You could try to work around this restriction by wrapping the call to
current_user in a user-coded function:
createdBy generated always as ( myWrapperFunction() )
However, myWrapperFunction() issues SQL and you have to declare that
when you define the function. That prevents you from declaring
myWrapperFunction() as deterministic, and that, in turn, prevents you
from using myWrapperFunction() in a generation expression.
There may be some detour around these restrictions, but I can't think of
it right now.
Hope this is useful,
-Rick
Sylvain Leroux wrote:
Hi,
Following the thread started by Thomas Hill who needs to store a
generated value in a column, I was wondering if I can define my own
SEQUENCE (or /GENERATOR/) in DERBY. Allowing one to write:
CREATE TABLE Client (
CreatedBy varchar(30) GENERATED ALWAYS AS MyCustomGeneratedValue(),
-- ^^^^^^^^^^^^^^^^^^^
Of course, I could use:
CREATE TABLE Client (
CreatedBy varchar(30) DEFAULT MyCustomGeneratedValue(),
-- ^^^^^^^
But, unlike other defaults, with GENERATED ALWAYS, the user cannot
insert a value directly into, or update, the generated value.
Best regards,
Sylvain
