PostgreSQL Temp Schema

This is cool, and I just discovered it. It is a Postgresql feature, not an RdbHost feature, but worth sharing. It is quite useful on Rdbhost databases.

There is a temporary schema, called pg_temp, created for each session (as necessary), and destroyed with its contents at the end of the session.

There is no TEMPORARY clause in function definitions, but you can get the same (or similar) result by putting the function in the pg_temp schema.

CREATE FUNCTION pg_temp.add(_one INTEGER, _two INTEGER)
  RETURNS INTEGER
AS $$ BEGIN
  RETURN _one + _two;
END; $$
LANGUAGE plpgsql VOLATILE SECURITY INVOKER;

SELECT pg_temp.add(1, 2) AS three;

On RdbHost, each request is its own session, so you can define a pg_temp function, use it immediately in the same request, and it conveniently disappears. Consider it an alternative to DO ... for running Pl/pgSQL queries in an ad-hoc way.

This alternative is especially useful for RdbHost queries, because it avoids the parameter-less quality of DO .. statements. RdbHost does not honor substitution tokens (%(name)s or %s) in literal strings, and DO .. statements put all the meat in a string, where tokens do not work. Functions also put their body in strings, but they support named parameters, so token values can be passed in.

This will not work, because the token %(val)s is embedded in a dollar-quoted string:

DO AS $$
    SELECT %(val)s + 1 AS inced;
$$;

but this will:

CREATE OR REPLACE FUNCTION "pg_temp"."addone" (IN _val integer)
    RETURNS integer
AS $$ BEGIN
    RETURN _val + 1 AS inced;
END; $$
LANGUAGE plpgsql VOLATILE SECURITY INVOKER;

SELECT inced FROM pg_temp.addone(%(val)s);

and neither one pollutes the permanent schema namespace with an additional function.

#

comments powered by Disqus