Example for simple plpgsql DO statement in db.registerLanguage wiki ?

[skilbjo]

hi,

core question

I am trying to have pg-mem run this sql code during a db.public.migrate(...) call:

  do $$
  begin
    if not exists (select 1 from pg_type where typname = 'transaction_type') then
      create type transaction_type as enum ('charge', 'refund');
    end if;
  end
  $$;

I want to have pg-mem do this via a mock:

db.registerLanguage('plpgsql', ({ code, args, returns }) => {
  return () => {
      // code that implements: `create type transaction_type as enum ('charge', 'refund');`
      // ??
    }; 
});

a suggestion or request could be: could you provide a simple example to implement things that have already been implemented in pg-mem via a registerLanguage call? or is that a non-trivial/bad question to ask?

investigation i did

I read about declaring custom languages (ie, plpgsql) from the wiki here: https://github.com/oguimbal/pg-mem/wiki/Custom-language-declaration-(functions,-do-statements,-plpgsql,-plv8,-...)

I also see some good test coverage: https://github.com/oguimbal/pg-mem/blob/master/src/tests/function-calls.spec.ts#L34

        db.registerLanguage('mylang', ({ code, args, returns }) => {
            expect(code).to.equal('some code');
            expect(args.map(x => x.type.primary)).to.deep.equal([DataType.text]);
            expect(returns?.primary).to.equal(DataType.text);
            return arg => {
                return 'hello ' + arg;
            }
        });

        none(`CREATE FUNCTION "sayHello"(in arg text) RETURNS text
        AS $$some code$$
        LANGUAGE mylang`);

        expect(many(`select "sayHello"('world')`))
            .to.deep.equal([{ sayHello: 'hello world' }]);

I see in the code where a custom enum is built in pg-mem, in t-custom-enum: https://github.com/oguimbal/pg-mem/blob/master/src/datatypes/t-custom-enum.ts

which calls t-reg-type: https://github.com/oguimbal/pg-mem/blob/master/src/datatypes/t-regtype.ts

it looks like the core of what I want to do (register an enum) is via this implementation:

    doBuildFrom(value: Evaluator, from: _IType): Evaluator<RegClass> | nil {
        switch (from.primary) {
            case DataType.text:
                return value
                    .setType(Types.regtype)
                    .setConversion((str: string) => {
                        let repl = str.replace(/["\s]+/g, '');
                        if (repl.startsWith('pg_catalog.')) {
                            repl = repl.substr('pg_catalog.'.length);
                        }
                        return value.owner.parseType(repl).name;
                    }
                        , strToRegType => ({ strToRegType }));
        }

but am unable to reason about how I could go about implementing something so simple as a mock return value (create the enum type) for create type transaction_type as enum ('charge', 'refund');

[oguimbal]

Hi !

The pgsql language is not implemented, but it doesnt mean that you cannot execute simple queries (that are supported).

So my answer may be naïve, but did you try something like this ?

db.registerLanguage('plpgsql', ({ code, args, returns }) => {

  assert(code).to.equal(expected); // or whatever

  return () => {
       if (!db.public.one(`select 1 from pg_type where typname = 'transaction_type'`)) {
           db.public.none(`create type transaction_type as enum ('charge', 'refund');`);
        }
    }; 
});

[skilbjo]

Thank you @oguimbal ! That's perfect. This is exactly what I was looking for - just enough code / a simple example to get started. Do you mind adding a reference to this discussion in the wiki for future users?

I tweaked it slightly, it's obviously not a full implementation or anything of the like, but if you have 1 or two plpgsql commands that need to be mocked, this will do. Here's what I finalized on:

postgres.test.ts

    db.registerLanguage('plpgsql', ({ code, args, returns }) => {
      const createTxnTypeEnum = `
  begin
    if not exists (select 1 from pg_type where typname = 'transaction_type') then
      create type transaction_type as enum ('charge','refund');
    end if;
  end
  `;

      switch (code) {
        case createTxnTypeEnum: {
          return () => {
            if (
              !db.public.one(
                `select 1 from pg_type where typname = 'transaction_type'`
              )
            ) {
              db.public.none(
                `create type transaction_type as enum ('charge', 'refund');`
              );
            }
          };
        }
        default:
          return () => {
            throw new Error(`This plpgsql has not been implemented. Provide an implementation.
                                         ${JSON.stringify({
                                           args,
                                           code,
                                           returns,
                                         })}`);
          };
      }
    });

001-init.sql

  do $$
  begin
    if not exists (select 1 from pg_type where typname = 'transaction_type') then
      create type transaction_type as enum ('charge', 'refund');
    end if;
  end
  $$;