jschaf / pggen

Generate type-safe Go for any Postgres query. If Postgres can run the query, pggen can generate code for it.
MIT License
287 stars 26 forks source link

Validate idea (result columns by view creation) WIP #38

Closed aight8 closed 2 years ago

aight8 commented 3 years ago

investigate and validate:

jschaf commented 3 years ago

Interesting, so the idea is given a source query like SELECT 3 * pggen.arg('foo'), create a view like:

CREATE VIEW pggen_foo_view AS
SELECT 3 * null -- or a default value
  1. Yep, I think it would only work for select queries. Maybe you can put inserts and updates in CTEs?

  2. Currently, pggen runs the query using null for all the parameters. Null has the nice property of working for all values. I worked on that section code of yesterday. The reason null works is that Postgres still reports the types even if there was a constraint violation caused by using null.

  3. I'm not sure if the column analysis with a view is more informative than by running the query. Maybe there's something interesting in the catalog tables?

I think the most promising method is to use the explain plan. pggen already has the explain plan and uses it a bit in inferOutputNullability.

jschaf commented 2 years ago

I think there's too many potential issues to pursue it: