Postgres : create function in a particular database

Question

Code :

CREATE FUNCTION square_num(num integer)
 RETURNS INTEGER AS $$

BEGIN

 IF (num<0) THEN
 RETURN 0;
 END IF;

 RETURN num*num;

END; $$
LANGUAGE PLPGSQL;

The above code created a function in the database postgres and schema public. How can I create a function in a particular database and it's schema? Thanks.

Answer 1

The above code created a function in the database postgres and schema public.

No. It creates function in the database you are connected to in a first existing schema from search_path.

Example:

-bash-4.2$ psql t
psql (10.1)
Type "help" for help.

t=# create table tt();
CREATE TABLE
t=# select table_catalog,table_schema from information_schema.tables where table_name = 'tt';
 table_catalog | table_schema
---------------+--------------
 t             | public
(1 row)

I defined database name t on connection, so relation is created in database t. I don't have schema postgres,so $user was skipped, next "default" schema is public:

t=# show search_path;
   search_path
-----------------
 "$user", public
(1 row)

so now:

t=# create schema postgres;
CREATE SCHEMA
t=# create table tt();
CREATE TABLE

see - no error that relation already exists, because:

t=# select table_catalog,table_schema from information_schema.tables where table_name = 'tt';
 table_catalog | table_schema
---------------+--------------
 t             | public
 t             | postgres
(2 rows)

function creation follows same rules, I used table as shorter syntax...

reading: https://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH

The first schema in the search path that exists is the default location for creating new objects.

finally answering

How can I create a function in a particular database and it's schema?

connect to needed database and either explicitely specify schema name:

CREATE FUNCTION my_schema.square_num(...and so on

or adjust search_path to meet your needs

update for sake of clarity I used schema name postgres to comply with original post. both using postgres database and creating postgres schema can be confusing for new users. There is nothing special (or system) about the default postgres database (that can be recreated any time from template), neither the name postgres gives any special attributes to schema. I only used it for OP to easier reproduce the example (as clearly from the fact that he connect to postgres database, user probably did not specify database name, connecting as OS user postgres). Thus to demonstrate how search_path first value $user was picked up, I used same name as username ...