Included below: 1) Question regarding the ability to inline set-returning functions 2) A comment that not keeping the content between the "CREATE VIEW ... AS" and the trailing ";|EOF" is losing good information to have inside the database. > Correct. The reparse time per se is generally not a big deal, but the > execution penalty associated with a function can be. If you go this > way you'll want to make sure that your function can be "inlined" --- > use EXPLAIN to make sure you get a plan matching the bare view, and > not just something that says "Function Scan". >>>>>> Is this even possible??? <<<<<< Related Question: If an inlined function is called and not all of its output columns referenced does the planner avoid pulling the data for the not-referenced columns? E.G.: SELECT col1, col2 FROM function_with_three_cols(); Does the planner ignore whatever "col3" would resolve to or are the contents of "col3" outputted in the "sub-query" and then simply ignored further up the tree? If "col3" contains a significant amount of text then its inclusion or exclusion could significantly impact performance. I am guessing that it would have to process and return "col3". Trying to answer the previous question this one presented itself: I just tried a couple of very simple queries and couldn't get them give me a plan that wasn't a "Function Scan". Is it possible that only "scalar" functions can be inlined? CREATE OR REPLACE FUNCTION three_col_func() RETURNS TABLE (col1 varchar, col2 varchar, col3 varchar) AS $$ SELECT '1'::varchar, '2'::varchar, '3'::varchar; $$ LANGUAGE sql VOLATILE ROWS 1 ; EXPLAIN SELECT * FROM three_col_func() QUERY PLAN Function Scan on three_col_func (cost=0.25..0.26 rows=1 width=96) This is on 9.0.X > For various reasons, this often goes the wrong way. Views are often the > right way to go. +1 on your comment above -- the right way to do views (and > SQL in general) is to organize scripts and to try and avoid managing > everything through GUI tools. It works. > > merlin While this is generally sound advice having a read-only version of the comments available on the server has merit. Not everyone who uses the database needs or wants to have access to the source scripts in order to learn how/why a particular object works or what it is intended for. COMMENT ON xxx IS 'text'; For much of the "public API" commenting that is desired the above command works OK but it is not a valid substitute in many situations. Not really looking to get into a deep discussion on this topic at the moment but the point is that not maintaining the entire text between the "AS" and the final ";" causes a loss of information that has value being stored in the database and thus becomes accessible to - admittedly PostgreSQL specific - meta-data tools. For the OP: as Tom indicated in-lining is key; and you cannot make use of parameters. You also cannot add a trigger to a function like you can a VIEW - functions are read-only. My $0.02 David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
