Have you ever been in a situation when you needed a query to be generated and executed dynamically as a result of another query?

If yes, read further: there is a simple and elegant way to achieve that.

First, create this function:

CREATE OR REPLACE FUNCTION dselect(varchar)
  RETURNS SETOF record AS $$
    DECLARE rec record;
    BEGIN
      FOR rec IN EXECUTE $1 LOOP
        RETURN NEXT rec;
      END LOOP;
    END
  $$ LANGUAGE 'plpgsql';

Second, umm.. well, that's it.

Anything you pass as a parameter will be interpreted and executed as an SQL statement.

The function is SELECTable. That is, you can use it in SELECTs:

SELECT * FROM dselect('SELECT id, name FROM users') AS t(id int, name varchar);

Please note the "AS t(id int, name varchar)" part. Postgres has no idea about what this function returns, so a column definition list should be provided. If not, Postgres will complain:

SELECT * FROM dselect('SELECT id, name FROM users');
ERROR:  a column definition list is required for functions returning "record"

Of course, the column definition list depends on the query and should match the actual query results.

So why this function is needed at all?

Because in situations like this:

CREATE SCHEMA sc_foo;
CREATE TABLE sc_foo.activity (date date, descr text);
INSERT INTO sc_foo.activity (date, descr) VALUES ('2009-08-07', 'went there');
INSERT INTO sc_foo.activity (date, descr) VALUES ('2009-06-04', 'hanging around');

CREATE SCHEMA sc_bar;
CREATE TABLE sc_bar.activity (date date, descr text);
INSERT INTO sc_bar.activity (date, descr) VALUES ('2009-10-11', 'came here');

You can do this:

SELECT 
    nspname 
FROM
    pg_namespace 
WHERE
    nspname LIKE 'sc_%'  AND
    (SELECT date FROM dselect('SELECT max(date) FROM ' || nspname || '.activity') AS t(date date)) > '2009-09-09';

 nspname 
---------
 sc_bar
(1 row)

In one pass this query goes over all the schemas whose names start with 'sc_', grabs the latest date from the schema's activity tables and matches the result against the provided date.

Of course, this approach is quite ineffective. Each time the function is called, a query is parsed and executed using a separate plan. A simple UNION of two queries would do the same, but... what if there are ten schemas? How about a hundred? I'm actually working with a database containing thousands of them.

I use this function when I need to collect statistics or do some db administration tasks, it saves me a lot of time.