Generating a types for function

[mikolajGon]

Is it possible also to add option, to extract types for postgres functions? function name, parameters types and return types ?

[kristiandupont]

I am considering this myself. I am trying to move some essential parts of business logic into the database and I would love for my code base for deal with that easily. Currently, I am bothered that views lose the knowledge of indices. I will try and look into your request after that.

[mikolajGon]

Splendid! Exactly same case for me. I actually manipulate and retrieve programaticcaly data in database only by calling functions.

[meticoeus]

I should have time to attempt this in the next couple weeks. I need this to finish off typing the features I use currently.

[meticoeus]

I haven't had time to finish a prototype for this item yet but I did find a few concerns while working on one. As far as I can tell there isn't a way to indicate nullability (just is-optional) in PG function arguments which would lead to less than ideal types in TS.

My current idea would be to add an option to the config to control default nullability (name: functionArgumentsNullable: boolean). If true, all args are nullable. If false (default), all args are non-nullable. For customization we parse the function comment and look for arg tags:

COMMENT ON FUNCTION some_function IS
  E'This function does something.
 @arg:foo @type:Foo @nullable
 @arg:bar @non-nullable
 @returns @type:Bar @non-nullable
';

I plan to parse this by checking if in the first tag on a line is one of arg or return then all remaining tags on the line as pertaining to the named argument or the return value. If there are named OUT arguments then returns @type would be prioritized if present. Otherwise an object would be created with the named OUT arguments defaulting to null or not null based on the functionArgumentsNullable option.

If there isn't an @nullable or @non-nullable tag then the argument uses the default according to the functionArgumentsNullable option. With this approach the user only needs to document exceptions to the rule. This also gives a way to add other tags to arguments such as specified types.

I haven't had a reason to use variadic function args in PG so far so I'm not sure if there are pitfalls for typing those yet.

@kristiandupont Any feedback on the above approach?

[kristiandupont]

So I've implemented a very lightweight view column inference in extract-pg-schema that attempts to parse the SQL expression that the view consists of. That approach has some obvious pitfalls but it does seem to work for now and I am wondering if we can do something similar for functions so that some of this plumbing wouldn't be necessary. I haven't looked into it whatsoever (and I now regret stating that "I will get around to this soon" further up -- my apologies!).

I don't know if PG exposes sufficient information to do this. If not, then your approach is probably the way to go. I am wondering though if we could lean more on some existing syntax, either JSDoc or maybe Typescript (@signature:(foo:Foo | null, bar) => Bar) or something..

[meticoeus]

As far as I can tell Postgres provides no way to directly add comments to function arguments : ( This leaves the only practical option as putting the documentation in the function comment. I've dug through the postgres docs section on comments and the information schema and related meta tables so I don't think I've missed anything. I'd be happy to be wrong though. It would really simplify things.

From what I recall when I first started transitioning from JSDoc to TS they have some tricky edge cases where they don't map nicely. Sticking to just typescript seems cleaner. TS in. TS out. The signature tag would require documenting the entire signature every time your function has a single argument that doesn't match your default settings so I think considering support to document one-off arguments is still worthwhile for complex functions with more arguments.

Suggesting a hybrid approach, we use signature for detailing the whole signature in the function comment in TS. We also use TS for individual argument tags:

// define the whole function signature
@signature:(foo:Foo | null, bar) => Bar\n
//^ tag    \___to typescript parser___/

// longer tag name for documentation clarity? must be one tag per line
@argument: foo?: Foo | null\n
//^ tag   \_to typescript_/ Pass contents into TS lexer/parser and use AST to determine types, hopefully

// shorthand. must still be one tag per line if not using signature
@arg:foo?:Foo|null\n

I haven't worked with typescripts parser yet but as long as it passes back a sensible AST this should be manageable.

I'll work on just handling the signature tag for a first PR when I get a chance to work on this more.