News

Welcome to End Point’s blog

Ongoing observations by End Point people

PostgreSQL: Dynamic SQL Function

Sometimes when you're doing something in SQL, you find yourself doing something repetitive, which naturally lends itself to the desire to abstract out the boring parts. This pattern is often prevalent when doing maintenance-related tasks such as creating or otherwise modifying DDL in a systematic kind of way. If you've ever thought, "Hey, I could write a query to handle this," then you're probably looking for dynamic SQL.

The standard approach to using dynamic SQL in PostgreSQL is plpgsql's EXECUTE function, which takes a text argument as the SQL statement to execute. One technique fairly well-known on the #postgresql IRC channel is to create a function which essentially wraps the EXECUTE statement, commonly known as exec(). Here is the definition of exec():

CREATE FUNCTION exec(text) RETURNS text AS $$ BEGIN EXECUTE $1; RETURN $1; END $$ LANGUAGE plpgsql;

Using exec() then takes the form of a SELECT query with the appropriately generated query to be executed passed as the sole argument. We return the generated query text as an ease in auditing the actually executed results. Some examples:

SELECT exec('CREATE TABLE partition_' || generate_series(1,100) || ' (LIKE original_table)');
SELECT exec('ALTER TABLE ' || quote_identifier(attrelid::regclass) || ' DROP COLUMN foo') FROM pg_attribute WHERE attname = 'foo';

Some notes about the exec() function: since the generated SQL statement is being run inside a function, it is not run in a top-level transaction, so some commands will not work, including CREATE/DROP DATABASE, ALTER TABLESPACE, VACUUM, etc.

Starting in PostgreSQL 9.0, the plpgsql language will be pre-installed in all new databases, which will make this recipe even easier to use.

6 comments:

David Fetter said...

As of 9.0, you'll be able to use the very nice DO command for dynamic operations :)

Frank said...

Be careful with permissions, only a superuser should have access to such a powerfull function.

David Christensen said...

Hi David Fetter,

I was going to mention DO, but then I wasn't familiar enough with it to know if you can dynamically construct the code argument. It appears to be hard-coded as a literal string in the grammar rather than an expression. I played around a bit trying to use CTEs to get DO to execute even with a single string argument, but was not able to do so.

Do you have an example of using DO to do something like this?

Thanks,

David

David Christensen said...

Frank,

That's generally a good point, however since the default PostgreSQL function security context is SECURITY INVOKER, this won't let you run any SQL command that you wouldn't be able to otherwise run manually. (Now SECURITY DEFINER on the other hand...)

Thanks for the comments,

David

David Fetter said...

Here's something to do the partitions. I haven't included the marshalling functions and trigger(s), but it would be pretty straight-forward.

Is there some way to format this code for your blog?

create table original_table(
t TEXT,
ts TIMESTAMPTZ
);

DO LANGUAGE plpgsql $$
DECLARE
i RECORD;
BEGIN
FOR i IN SELECT now()+generate_series(0,23)*interval '1 month' AS d LOOP
EXECUTE
'CREATE TABLE partition_' || to_char(i.d, 'YYYYMM') ||
'(LIKE original_table, CHECK(ts >= ''' || date_trunc('month', i.d) ||
''' AND ts < ''' || date_trunc('month', i.d+interval '1 month') || '''))';
END LOOP;
END;
$$;

Anonymous said...

Is there a way to use something like this to do a dynamic SELECT statement?