add a `pggen.optional()` directive

[breathe]

It would be nice to be able to explicitly signal when an argument can be null.

I want to be able to explicitly use NULL values to turn off the effect of 'optional' where clauses for some queries

eg: select 1 as a where (pggen.arg('foo')::bigint is NULL OR pggen.arg('foo')::bigint = a);

But the default inference for this query will infer a non-pointer type for 'foo' above ... It'd be great if I could instead write something like:

select 1 as a where (pggen.optional('foo')::bigint is NULL OR pggen.optional('foo')::bigint = a);

and be given a pointer type so that my go program can pass nil to the query and be certain that the value will be received as NULL by postgres.

[jschaf]

The problem is that inputs types are always assumed to be non-null: https://github.com/jschaf/pggen/blob/2489d54a806652d73f2c83bb5736a7574582092c/internal/codegen/golang/templater.go#L147

I probably can't change that decision without breaking existing code.

We use sentinel values as a stand-in for null with a few helper functions. This works pretty well with Go's default values. Here's what it looks like:

SELECT
  default_if_empty(pggen.arg('foo'), 'my_default_value')

If Foo is an empty string, it'll trigger the default value. Here's the function definition (overloaded with other types):

CREATE FUNCTION default_if_empty(s text, default_str text) RETURNS text AS $$
SELECT coalesce(nullif(s, ''), default_str)
$$ LANGUAGE sql IMMUTABLE PARALLEL SAFE;

[breathe]

Closing this - I'm gathering there is no interest to accept this

[jschaf]

Hi there, I'm interested but life is a bit crazy (we just had our second kiddo). I like the idea of pggen.optional with maybe some tweaks to naming. Optional args fill a gap in pggen.

sqlc calls this sqlc.narg https://docs.sqlc.dev/en/stable/howto/named_parameters.html

[breathe]

Congratulations on the baby! 🍼

When you have time to circle back around to this, feel free to drop your thoughts and I'm happy to update that PR as needed. :)

Cheers!

[DavidArchibald]

I added this along with some other features over at https://github.com/mypricehealth/pggen/commit/5ed39855f989872685925631443e24df76e705c6.

I don't plan to keep this as a separate fork forever but I'd consider what I'm doing there pretty experimental and I need to discuss with jschaf how to get my changes merged in and which ones they like (once they're well rested of course!).