Pg-promise: Best practice for dynamic column names?

[MattBz]

One time the query should look like

SELECT colum1 FROM table...
INNER JOIN...

but it should also be able to look like this

SELECT colum1, colum2, column3,
INNER JOIN ...
LEFT JOIN...

Can this be done with query files? If not, how would it look like in Javascript?

Thanks!

[vitaly-t]

It can very easily be done within SQL files...

SELECT ${columns:name} FROM table...
INNER JOIN...

in JavaScript:

const values = {
    columns: ['column1']
};

if (/*need to add columns*/) {
    values.columns.push('column2', 'column3');
}

Filter :name, for [SQL Names], has a special logic to it, and it can be:

• a simple text string, which is normal, to represent a single column name
• string * (asterisks), which is detected automatically
• array of strings, in which case it is considered a list of column names
• any object, to pull all property names automatically.

In our example we use the latter to provide a list of columns.

---

Here's another example, when by default we would select all columns, and only on a condition we would select specific columns:

const values = {
    columns: '*' // all columns
};

if (/*need specific columns*/) {
    values.columns = ['column1', 'column2', 'column3'];
}

[vitaly-t]

Following this question, I did a complete rewrite of the [SQL Names] documentation, which now explains everything nicely, with examples.

[MattBz]

Thank you for your detailed answer!

Regarding adding addtional JOINS and other parts of the query dynamically . Would I have to use "Raw Text" then?

[vitaly-t]

Regarding adding addtional JOINS and other parts of the query dynamically . Would I have to use "Raw Text" then?

Yes, you can use [Raw Text] for that.

Alternatively, if other parts are too complex, you can always have your query file use another query file, i.e. as a composite query file.

[MattBz]

Okay, thank you!