Anders Brownworth

Technology and Disruption

Postgres Extensions and Functions in Schemas

Adding an extension to PostgreSQL usually implicitly sets up some functions enabling use of the extension. The extensions are global but the functions created to make use of the extension are schema specific. Therefore, if you do this:

SET search_path = reports;
CREATE EXTENSION "uuid-ossp";

you are implicitly creating these functions in the "reports" schema:

uuid_generate_v1()
uuid_generate_v1mc()
uuid_generate_v3()
uuid_generate_v4()
uuid_generate_v5()
uuid_nil()
uuid_ns_dns()
uuid_ns_oid()
uuid_ns_url()
uuid_ns_x500()

You can see what functions are within a particular schema in psql using:

\df reports.*

If you need to use these functions in other schemas, you have to either fully qualify the function:

SELECT reports.uuid_generate_v4();

or you have to get a copy of the function which uses the global extension in the schema you are in:

db=# set search_path=marketing;
SET
db=# CREATE FUNCTION uuid_v4()
db-# RETURNS uuid
db-# LANGUAGE c
db-# STRICT
db-# AS '$libdir/uuid-ossp', $function$uuid_generate_v4$function$;
CREATE FUNCTION
db=# SELECT marketing.uuid_v4();
uuid_v4
--------------------------------------
33675b19-aa8b-41b5-b07c-06e3f774e588
(1 row)

Another way to go would be to create a schema called "extensions" and do your extension install there which would make all your functions live there too. Then anywhere you needed to use an extension, you would qualify it with an "extensions." prefix.

CREATE TABLE foo (
id uuid DEFAULT extensions.uuid_generate_v4()
);

Comments (0)

Leave a Comment

Name:
Location: (city / state / country)
Email: (not published / no spam)
Comment:

No HTML is allowed. Cookies must be enabled to post. Your comment will appear on this page after a moderator OKs it. Offensive content will not be published.

Click the puppy to submit your comment.

To create links in comments:
[link:http://anders.com/] becomes http://anders.com/
[link:http://anders.com/|Anders.com] becomes Anders.com
Notice there is no rel="nofollow" in these hrefs. Links in comments will carry page rank from this site so only link to things worthy of people's attention.