Hi Community! I am in the process of re-writing SQLs in app code to a more DB side approach.
My first idea was to write views, and let the app query those views; i'd need to rewrite some queries to expose the "parameters" (where condition fields) of those queries in the select part, to expose them and allow app to filter on them. Now a huge issue. But, i might also create functions (language sql) returning "RETURNS TABLE", and pass the where conditions as parameter; i would have to bite the bullet and write all the "RETURNS TABLE (... ... ...)" definitions, but meh. :-D My question would be: in terms of performance, the select on the function looks way faster, but I am afraid PG optimized does not really "understands" the complexity of the query. For example, the explain on the view understands that the query returns always 1 row; the explain on the proc. does not. On the other side, functions can be tuned with many many "hints" (COST, ROWS, etc...) In my shoes, what would you do? Thanks a ton!
