Map String into ColumnSet

[kduraiswami]

Hello I have an array of strings that I need to map into a ColumnSet manually:

let columnNames = [
"Status",
"Account Name"
"Account Number", 
... 
"Warning"
]

I am trying to go through these and create a ColumnSet

let insertColumnSet = new pgp.helpers.ColumnSet(
                tableConfig.columns.map(column => {
                    return {
                        name: column, 
                        prop: "_",
                        init: (col) => {
                            return col.source[column]
                        }
                    }
                }),
                {table: tableConfig.tableName}                
            );

            let insertQuery = pgp.helpers.insert(someData, insertColumnSet);

This will pass the compiler but unfortunately when it runs the insert statement it will ONLY access the last property for each object which is "Warning." I have tried to clone the string value and use a for loop instead of mapping. I can see when I print the ColumnSet that each name is correct too.

ColumnSet {
    table: "account"
    columns: [
        Column {
            name: "Status"
            prop: "_"
            init: [Function]
        }
        Column {
            name: "Account Number"
            prop: "_"
            init: [Function]
        }
        Column {
            name: "Account Name"
            prop: "_"
            init: [Function]
        }
       ...
       Column {
              name: "Warnings"
              prop: "_"
              init: [Function]
          }
      ]
}

Any help is much appreciated on how to access the correct property when mapping the ColumnSet from a string array of names

[vitaly-t]

There is something odd in your example about the prop usage. Making it the same for all columns confuses the parser. If you make it unique, then it works:

let insertColumnSet = new pgp.helpers.ColumnSet(
                tableConfig.columns.map((column, idx) => {
                    return {
                        name: column, 
                        prop: "_" + idx,
                        init: (col) => {
                            return col.source[column]
                        }
                    }
                }),
                {table: tableConfig.tableName}                
            );

I will look at it later, to figure out why prop is confused in this case. You have your working example for the time being.

[vitaly-t]

Are you actually using column names with spaces in your database? That's generally ill-advised.

[kduraiswami]

Hey, this is awesome, I just checked and it was able to get the correct properties if I added the index to the prop. Yeah of course it is a bad practice, but I am doing this on a migration that has to be exactly the same for many upstream processes on top. I will try to find the Github issue that had an example with prop simply as an "_" and show you.

[kduraiswami]

I took it from here - #494 (comment)

[vitaly-t]

Yes, I saw it, and there's something off there. In theory, when init is used, prop should be ignored altogether. I will look into that later, and post an update.

[vitaly-t]

So I have done some investigation, since I did the formatting logic years ago, and started forgetting it myself.

ColumnSet value formatter relies on Named Parameters, and those strictly adhere to JavaScript variable notation, and as such do not support spaces in the names. So when prop is provided, it is used for producing the formatting template, and otherwise it defaults to name.

In your specific case, name is always an invalid name for a JavaScript variable, so in order to produce a valid formatting template, prop is needed, just for internal template reference, while the column name isn't used when pulling values, as you would use init for that instead.

And so you end up using some random, but importantly unique property names, and then the whole thing works.

None of this is ever necessary when you either use proper valid column names or at least simple property references, through re-mapping, like shown below:

const updatedData = someData.map(a => ({
    accountName: a['Account Name'],
    accountNumber: a['Account Number']
}));

And then inside your ColumnSet you do not need to use init, you can just use prop:

const insertColumnSet = new pgp.helpers.ColumnSet([
    {
        name: 'Account Name',
        prop: 'accountName'
    },
    {
        name: 'Account Number',
        prop: 'accountNumber'
    }
],
{table: tableConfig.tableName});

And that's it.

[vitaly-t]

@kduraiswami Have you tried this approach?

[kduraiswami]

Hey, it worked with including the index on the prop to make it unique which was good enough for me. I don't want to map and cast everything since I am only moving it from one place to another. I am sure your approach would work though as it would confirm to Javascript variable name.