Today another two new additions to PostgreSQL – as You can see may commit-fest seems to work pretty good 🙂

First change is new functionality in pl/pgsql: RETURN QUERY EXECUTE.

Pavel Stehule, and Tom Lane committed patch which lets You use new command, or to be more specific extend existing command.

“RETURN QUERY" was added in PostgreSQL 8.3, and I've written about it then. The thing is, that it allowed You to return only standard non-dynamic queries.

With this new command “RETURN QUERY EXECUTE" You can use generated queries. It looks like this:

CREATE OR REPLACE FUNCTION last_objects(
    IN TABLE_NAME TEXT, IN objects INT4,
    OUT object_name TEXT, OUT object_id OID
) RETURNS SETOF RECORD AS $BODY$
DECLARE
    use_field TEXT;
    use_sql TEXT;
BEGIN
    -- This select will get name of first column in given table.
    SELECT a.attname INTO use_field
        FROM pg_attribute a JOIN pg_class c ON a.attrelid = c.oid
        WHERE c.relname = TABLE_NAME AND a.attnum > 0
        ORDER BY a.attnum ASC LIMIT 1;
    -- Following query will get last (sorted by oid) rows from given table, and return it's first column along with associated oid.
    use_sql := 'SELECT ' || quote_ident(use_field) || '::TEXT, oid FROM ' || quote_ident(TABLE_NAME) || ' ORDER BY oid DESC LIMIT ' || objects::TEXT;
    raise notice 'Running query: %', use_sql;
    RETURN QUERY EXECUTE use_sql;
END;
$BODY$ LANGUAGE plpgsql;

This function will return last X object names from given system catalog table, along with their OIDs:

# SELECT * FROM last_objects('pg_proc', 2);
NOTICE:  Running query: SELECT proname::TEXT, oid FROM pg_proc ORDER BY oid DESC LIMIT 2
 object_name  | object_id
--------------+-----------
 last_objects |     18517
 sample       |     18514
(2 ROWS)

# SELECT * FROM last_objects('pg_class', 2);
NOTICE:  Running query: SELECT relname::TEXT, oid FROM pg_class ORDER BY oid DESC LIMIT 2
    object_name     | object_id
--------------------+-----------
 users_username_key |     18433
 users_pkey         |     18431
(2 ROWS)

Of course, before “RETURN QUERY EXECUTE" was introduced You could also do the same thing with:

FOREACH record IN EXECUTE use_sql LOOP
    RETURN NEXT record;
END LOOP;

But the syntax with RETURN QUERY EXECUTE is much simpler and most probably faster.

Second patch, that I decided to describe today, was written by Robert Hell, and committed also by Tom Lane.

It's commit message seems pretty simple:

Allow the planner's estimate of the fraction of a cursor's rows that will be
retrieved to be controlled through a GUC variable.

But what it really means?

Unfortunately I'm not able to show You real example, but I'll try to describe it.

Basically, this new GUC will be used if:

• You use cursors
• You don't fetch all rows from cursor
• number of rows to be fetched is not known in advance, but it is possible to estimate.

Very simple example would be – get data from table sorted by some field, get only 10 first rows, but if 11th (or 12th, or ..) has the same value in sort field – get it as well.

This means You can't simply use LIMIT, and so You have to write Your cursor in a way that it will return all rows.

Now, it is entirely possible that if You would get only 10 rows planner would use another, more efficient plan.

This new GUC can be used to say “I estimate that I will get at most 5% rows from cursor". Cursor will still “have" all data, but planner will use this new knowledge to choose plan that might be slower for getting all data, but is faster in case You're getting just a small fraction of them.

Default value for this new variable is 0.1, which means planner will estimate that if You define a cursor You will (statistically) get only 10% of rows from it.