Hi

You describe a very common pattern in database applcation environments,
which are covered by tool specific migration scripts. (e.g. see Ruby-on-Rails which
takes it to the extreme, SQLalchemy, and most web-based application frameworks)

In general, running a start-up script automatically adds limited extra functionality
to the server and would severely complicate the code base to accommodate this for general use,
e.g. where are the scripts located in the environment (Linux,Mac,Win), what OS access
credentials are available, does it depend on OS libraries, is there proprietary code being called,....
Other systems expect full control over the environment and also rely on running scripts as part of their own code to initialize a system.
This is how we use it by manipulating the .../sql/scripts directory

A migration script is typically called from a shell script as an ordinary
user program. In general, such scripts could have much more preparatory work,
e.g. taking backups first.

In one case I made the following simple migration step function and ran it over a directory
of migration files ordered by date.

my penny

regards, Martin (the other one)

-- The template for SQALPEL migration steps

-- Migration steps are taken in the timestamp order.
-- The identifiers are prefixed with a date and align to the file names.

-- Beware, each migration script is ran in isolation without explicit knowledge
-- about the success of failure of previous steps.

-- call: mclient -d sqalpel -a <migrationfile>


create function migratesqalpel()
returns string
begin
     declare mid string;
     set mid = '20190000';  -- the migration identifer
     declare doit boolean;
     set doit = ((select count(*) from sqalpel.migrations where id =mid) = 0);

     if not doit
     then
         return 'Migration 20190000 skipped';
     end if;
     -- here comes the list of SQL actions to implement the migration

     -- Finalize with recording of the last step
     insert into sqalpel.migrations values(mid, now());
     return 'Migration 20190000 completed';

end;

select migratesqalpel();

drop function migratesqalpel();

On 22/04/2020 18:08, Martin van Dinther wrote:
> It is not possible in MonetDB SQL. Also not in the SQL standard.
> You will need to do such conditional DDL statements from an external program/script.
>
> Normally you would create all DB objects (schemas, tables, views, functions, procedures, etc) once (as monetdb admin) and next use/call them from your application.
> Is it not possible in your case to create all the needed functions once upfront?
> Why do you need to create them conditionally?
>
> Martin vD
>
> On 22-04-2020 15:58, Roberto Cornacchia wrote:
>> Hi,
>>
>> I am afraid I know the answer to this already ("not supported"), but I wanted to check with you guys. Perhaps someone has smart workaround?
>>
>> Essentially what I would like to do is something like:
>>
>> IF condition THEN CREATE FUNCTION ....;
>>
>> As far as I know, flow control statements are only allowed inside functions/procedures.
>> However, CREATE statements are not allowed inside functions/procedure.
>> So I don't see any way to do what I wrote above.
>>
>> Notice, I am aware of CREATE OR REPLACE. What I'm looking for is an arbitrary condition test.
>>
>> Any trick? And/or, any plan to implement something in this direction?
>>
>> Roberto
>>
>>
>> _______________________________________________
>> users-list mailing list
>> users-list@monetdb.org
>> https://www.monetdb.org/mailman/listinfo/users-list
>>
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list