2

I installed orafce extension on PostgreSQL 9.5. I saw that it is better to create a specific schema for all extensions and that what I did. I connected to template 1 and performed the next commands :

template1=# create schema extensions;
CREATE SCHEMA
template1=# grant usage on schema extensions to public;
GRANT
template1=# grant execute on all functions in schema extensions to public;
GRANT
template1=# alter default privileges in schema extensions grant execute on             
functions to public;
ALTER DEFAULT PRIVILEGES
template1=# alter default privileges in schema extensions grant usage on     
types to public;
ALTER DEFAULT PRIVILEGES
template1=# create extension orafce schema extensions;
CREATE EXTENSION
template1=# show search_path;
search_path
 -----------------
"$user", public
(1 row)

template1=# set search_path="$user",public,extensions;
SET

After that I created a new database test1 and a new user mariel1. In addition I edited postgresql.conf and set the search_path to be "$user",public,extensions.

I connected to the database - psql -d test1 -U mariel1. Now when I try to use the function sysdate for example the database doesn't recognize the function:

postgres=# select sysdate() from dual;
ERROR:  function sysdate() does not exist
LINE 1: select sysdate() from dual;
           ^
HINT:  No function matches the given name and argument types. You might need     
to add explicit type casts.
postgres=# select extensions.sysdate() from dual;
ERROR:  schema "extensions" does not exist
LINE 1: select extensions.sysdate() from dual;

After some searching I saw that some of the functions available under other schemas like oracle, utl_file and more. I want to understand why the functions of orafce (oracle.sysdate etc...) created under different schemas (oracle,utl_file..) and not under my new schema extensions.

halfer
  • 19,824
  • 17
  • 99
  • 186
JeyJ
  • 3,582
  • 4
  • 35
  • 83

1 Answers1

0

Extension in PostgreSQL is Database level not schema level or instance level. You cannot create same Extension in same database. But you can create same extension in multiple databases. that's how extension in PostgreSQL work.

Adrian Hartanto
  • 435
  • 2
  • 7
  • I created the extension in tempalte 1 which means it will be created in every database that i will create. My question is why isnt the extension installed in the schema that i specify but in more schemas ? – JeyJ Jul 05 '17 at 08:15
  • can i know what "\dx" output in template1 database and others database? – Adrian Hartanto Jul 06 '17 at 09:26
  • template1=# \dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+-------------------------------------------------------------------------------------------- --- orafce | 3.6 | extensions | Functions and operators that emulate a subset of functions and packages from the Oracle RDB MS plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language – JeyJ Jul 06 '17 at 09:38
  • okay i see it, your extension installed in extensions schema. can you show me "\dx" the other database ? – Adrian Hartanto Jul 06 '17 at 09:48
  • comb=# \dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+-------------------------------------------------------------------------------------------- --- orafce | 3.6 | extensions | Functions and operators that emulate a subset of functions and packages from the Oracle RDB MS plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows) – JeyJ Jul 06 '17 at 09:49
  • Okay under database comb and template1. can you show me "\dn" output – Adrian Hartanto Jul 06 '17 at 09:51
  • comb=# \dn List of schemas Name | Owner ------------------------+---------- bl_create_flat_file_pg | repdev control_reports_pg | repdev dbms_alert | postgres , dbms_assert | postgres, dbms_output | postgres dbms_pipe | postgres dbms_random | postgres dbms_utility | postgres extensions | postgres oracle | postgres utl_file | postgres – JeyJ Jul 06 '17 at 09:58
  • in comb i had 20 rows but i didnt have enough space (some schemas are specific for comb..) template1=# \dn List of schemas Name | Owner --------------+---------- dbms_alert | postgres dbms_assert | postgres dbms_output | postgres dbms_pipe | postgres dbms_random | postgres dbms_utility | postgres extensions | postgres oracle | postgres plunit | postgres plvchr | postgres plvdate | postgres plvlex | postgres plvstr | postgres plvsubst | postgres public | postgres utl_file | postgres – JeyJ Jul 06 '17 at 09:58
  • okay sir, i understand now? why your function created under because. when you execute "create extension orafce;" you execute orafce.so already compile in source code orafce--3.4.3.SQL thats is the function creation. if you want all the function created under "extensions" schema you should change the source_code. because when you "create extension orafce" the orafce.so will create function basesd on the default source_code which is multiple schema – Adrian Hartanto Jul 06 '17 at 10:13