Re: Views versus user-defined functions: formatting, comments, performance, etc.

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.