Pg-promise: optional parameters

[tkellen]

When using named parameters, what do you think about the possibility of allowing undefined parameters? If they were missing, they'd default to null.

UPDATE table
SET (fieldOne, fieldTwo) = (
  COALESCE(${fieldOne}, fieldOne),
  COALESCE(${fieldTwo}, fieldTwo)
);

Alternatively, can you suggest another approach?

[vitaly-t]

This would go against the logic of partial formatting where variables can be set in more than one step.

See parameter partial in the format method.

QueryFile relies on it usage, for example.

---

As for another approach... when you are providing an object for named parameter formatting, it normally has all the properties in it, so if some of them are not set undefined, you would get your NULL-s.

[vitaly-t]

In theory, we could have another parameter within the format function, say default, to provide default value for variables that do not exist.

But i'm not sure how much value it would add, since you do not have access to formatting options from query methods.

[vitaly-t]

@tkellen any feedback on this?

[vitaly-t]

Added option default in v.5.0.4

[tkellen]

hey @vitaly-t, you are too fast! thank you so much! can you share an example usage?

[vitaly-t]

Have you seen the updated API? It is quite self-explanatory: as.format.

[tkellen]

Indeed it is, thanks again @vitaly-t!

[vitaly-t]

@tkellen many things have changed with the release of v.5.2.0, in relation to what was discussed here, making it way simpler to use external SQL as templates of any nesting level.

[tkellen]

That's awesome @vitaly-t! Will you be spinning up any examples for this? I'm pretty sure I understand what you're describing based on the release notes but I haven't had a chance to try it yet.

[vitaly-t]

@tkellen you would be right to think of it in very simple terms, QueryFile is now a type that can be a formatting parameter, to inject sub-queries :) And you can use this type with method as.format directly.

If you try, I'm sure it will just work for right off the way you think it should ;) No hidden tricks there :)

[raine]

How does one pass postgres keyword default with { partial: true } or possibly { default: <something> }?

When I use pgp.as.format(query, { location: undefined }, { partial: true }) it leaves undefined values unchanged in the query, causing syntax error:

VALUES ('blah', $[location])
error: syntax error at or near "$"

To clarify, the use case is that I'm making a parameterized query and the passed values object may not contain all the parameterized values, in which case I'd like postgres to use the default value set in the table for that column. default keyword in postgres seems to allow that.

[vitaly-t]

@raine Can you clarify what kind of queries you are trying to build. Are those by any chance INSERT or UPDATE queries? If so, then you should be using the helpers, as they are the ones that support value overrides, etc.

Option default is there to provide values when those are missing completely. Values undefined or null do not signify missing properties, they are just values. So you cannot use any options within as.format to replace values, it is not what that method is for.

If in some unique case you still need to inject DEFAULT directly with method as.format, you would have to provide the following for the corresponding value:

const DEFAULT = {
    rawType: true,
    toPostgres: () => 'default'
};

That uses Custom Type Formatting to inject default as a raw text string.

[raine]

Oh yeah, the properties are actually missing, not set as undefined. That was just the example which I thought would clarify but I guess it had the opposite effect. 😁

[vitaly-t]

@raine In that case, as per my previous post...

const DEFAULT = {
    rawType: true,
    toPostgres: () => 'default'
};

pgp.as.format('Values: ${first}, ${second}', {}, {default: DEFAULT});
//=> Values: default, default

pgp.as.format('Values: $1, $2', [], {default: DEFAULT});
//=> Values: default, default

See Custom Type Formatting.