PostgreSQL 8.3 introduced a couple of new features that improves the processing of functions and makes plpgsql
functions easier to write. These are as follows:
The new ROWS and COST declarations for a function. These can be used for any PostgreSQL function written in any language. These declarations
allow the function designer to dictate to the planner how many records to expect and provide a hint as to how expensive a function call is. COST is measured in CPU cycles. A higher COST number means more costly.
For example a high cost function called in an AND where condition will not be called
if any of the less costly functions result in a false evaluation. The number of ROWs as well as COST will give the planner a better idea of
which strategy to use.
RETURN QUERY functionality was introduced as well and only applies to plpgsql written functions.
This is both an easier as well as a more efficient way of returning query results in plpgsql functions.
Hubert Lubazeuwski provides an example of this in set returning functions in 8.3.
We shall provide yet another example of this.
Server configuration parameters can now be set on a per-function basis. This is useful say in cases where you know a function will need a lot of work_mem, but you don't want to
give all queries accessing the database that greater level of worker memory or you are doing something that index scan just works much better than sequential scan and you want to
change the planners default behavior only for this function.
Plan Invalidation - Merlin Moncure covers this in PostgreSQL 8.3 Features: Plan Invalidation so we won't bother giving
another example of this. Basic point to take away from this is that in procedures where you have stale plans floating dependent on tables being dropped by a function, those plans will
be automagically deleted so you don't have ghost plans breaking your function.
Every once in a while you are tasked with an issue such as having to create logging fields
in each of your tables or having to put like constraints on each of your tables or you need to Grant an X group or X user rights to
a certain set of tables.
The nice thing about having an information_schema is that it provides an easy way to generate scripts to do just that with plain SELECT statements.
In PostgreSQL its even easier with the array_to_string functions and ARRAY functions, you can get the script in a single field result.
In the following sections we'll demonstrate some examples of this.
Question: How do you move tables and views from one schema to another?
Often times when you start a new database, you put all your tables and views in the public schema. For databases
with few tables and all relatively commonly grouped data, the benefits of using schemas for logical groupings vs. the downside
of having to reference it with the schema name is more trouble than its worth.
As time goes by and with organic growth, this simple database you had that does one thing suddenly starts doing a lot of other things
you hadn't initially planned for. Now to keep your sanity you really need the benefit of schemas for logical groupings. How do you
retroactively do this? The answer is not quite as easy as one would hope. Ideally you would want to do a RENAME from public.sometable to newschema.sometable,
but that doesn't work.