db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Custom sequences/generators
Date Fri, 21 Aug 2009 12:41:42 GMT
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
>


Mime
View raw message