Pgloader: [howto] Apply two transformations to a field?

[AnrDaemon]

I'm experimenting with CAST clause in DBF import, and stumbled upon the necessity to apply two transformations to the field.

My attempt was this:

LOAD DBF
    FROM '{{SOURCE}}'
        WITH ENCODING CP866
    INTO pgsql://{{POSTGRES_USER}}:{{POSTGRES_PASSWORD}}@{{POSTGRES_HOST}}/{{POSTGRES_DB}}
        TARGET TABLE "fias"."{{PARTITION}}"
        WITH create table
        CAST
            COLUMN "{{PARTITION}}"."previd" TO UUID USING (empty-string-to-null (right-trim previd))
…

…but source parser exploded with

In context CAST-FUNCTION:

While parsing FUNCTION-NAME. Problem:

  The production

    #\(

  does not satisfy the predicate PGLOADER.PARSER::FUNCTION-NAME-CHARACTER-P.

Expected:

     any character satisfying FUNCTION-NAME-CHARACTER-P

While parsing IGNORE-WHITESPACE. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the string "--"
  or the string "/*"

What I'm doing wrong?

[dimitri]

Yeah it's not accepted to use any expression here, only function names. You can define your function in a lisp file and then use option -l (--load-lisp-file) on the command line to make use of the function. See https://github.com/dimitri/pgloader/issues/82 for an example of using a lisp file. I n your case something like the following might work:

(in-package :pgloader.transforms)

(defun right-trim-then-empty-string-to-null (value)
  (empty-string-to-null (right-trim value)))

And then use pgloader -l yourfile.lisp foo.load and in the cast rule using right-trim-then-empty-string-to-null. That should work. Please open this issue again if that fails to solve your problem.

[AnrDaemon]

(in-package :pgloader.transforms)

(defun db3-string-to-nullable-uuid (value)
    (empty-string-to-null (right-trim value)))

Worked like a charm! Thanks!

[dimitri]

Thinking some more about it, I guess there's no strong reason to disallow expressions in the load file at the using casting rule clause, so I just made it happen. The expression needs to be funcallable, which in practice means it should be a lambda function definition, as in the following example:

LOAD DBF
    FROM data/DNORDOC.DBF with encoding cp866
    INTO postgresql:///pgloader
    TARGET TABLE public.dnordoc
    WITH truncate, create table, disable triggers

    cast
      column dnordoc.normdocid
          to uuid
      using (lambda (normdocid)
              (empty-string-to-null (right-trim normdocid))),
      column dnordoc.doctype to integer using db3-numeric-to-pgsql-integer;

[AnrDaemon]

The doctype transformation won't work. Your DBF driver does not support I, O or + types.

[dimitri]

I now see those data types at https://www.clicketyclick.dk/databases/xbase/format/data_types.html. The I for integer seems to work now, see #929 where I just added a fix. The documentation for the O (double) and + (auto increment) leaves too much guesswork for my taste, so please provide files using those formats if you have them, as test cases, for me to add support for those data types in cl-db3 and pgloader.

[AnrDaemon]

The data types also on http://www.dbase.com/KnowledgeBase/int/db7_file_fmt.htm (the very bottom part), that's where I've found them.
The description is scarce, but quite enough for me (except endianness of the I type, which seems wrong.)

[dimitri]

I have found DBF sample files with new data types and new formats too, so I have added those capabilities in cl-db3 and pgloader. I still didn't find sample files with the O data type though...