How to create database function in a migration?


#1

Hello, I want to create functions into my database as part of the migration process - is that the right place to do it?

The function definition works from the sql cmdLine - how can I do it from a migration?

'use strict'

/** @type {import('@adonisjs/lucid/src/Schema')} */
const Schema = use('Schema')

class GettersSchema extends Schema {
  up() {
    this
      .raw(`
        create or replace function builder.get_behavior()
        returns text AS
        $$
          return JSON.stringify(plv8.execute("select ojson from builder.supporting where otype = 'behavior' order by oversion desc limit 1")[0].ojson);
        $$ language plv8;
      `)
  }

  down() {
    this
      .raw(` drop function builder.get_behavior `)
  }
}

module.exports = GettersSchema

#2

what do you need to achieve with this?

Since migrations files are created to manage the tables creation to your database


#3

I gave up the idea - I remembered I don’t like code in the database because I can’t see it in a debugger anyway.