Considerations for Search_path functions in PostgreSQL

PostgreSQL, like most databases, logically divides the attribution of objects through schemas, as shown in the graph.

Because of the concept of schema, when accessing objects in a database, we need to specify which schema is underneath.

such as schema_a.table.

For the convenience of users, PostgreSQL also has a client environment parameter called Search_path, which controls the order of the search path.

For example, Search_path= ' $user, public ' indicates that a schema with the same name as the current user name is searched for, and if not, the public schema is searched.

For example, the current user is a, there is a table in the database a.tbl and a table public.tbl. Then select * from TBL, Access is a.tbl.

function Writing Considerations
When we write a function, it is easy to overlook the point that is the search path.

Like, I wrote a function like this.

sanity=> CREATE OR REPLACE FUNCTION "Digoal". Getbusinessname "(" Ptypeno "varchar)
RETURNS "Pg_catalog". VarChar "as $BODY $

BEGIN

Return (select TypeName from Tbl_core_bus where typeno= Ptypeno);

End;

$BODY $
LANGUAGE ' Plpgsql ' VOLATILE;
CREATE FUNCTION

will be a bit messy oh, tbl_core_bus table in the end is which schema?
is related to Search_path, so if you don't have this table in Search_path, accessing this function will cause an error.

Therefore, it is important to note that when writing a function, use a schema to avoid this problem.

The above function can be changed as follows, preceded by a schema representation of the Access object

The CREATE OR REPLACE FUNCTION "Digoal". Getbusinessname "(" Ptypeno "varchar)
RETURNS "Pg_catalog". VarChar "as $BODY $

BEGIN

Return (select TypeName from Digoal.tbl_core_bus where typeno= Ptypeno);

End;

$BODY $
LANGUAGE ' Plpgsql ' VOLATILE;
or change to the following, set the parameters of the function Search_path

The CREATE OR REPLACE FUNCTION "Digoal". Getbusinessname "(" Ptypeno "varchar)
RETURNS "Pg_catalog". VarChar "as $BODY $

BEGIN

Return (select TypeName from Tbl_core_bus where typeno= Ptypeno);

End;

$BODY $
LANGUAGE ' Plpgsql ' VOLATILE set search_path= ' Digoal ';

Or instead, use the SET command within the function to set the Search_path

The CREATE OR REPLACE FUNCTION "Digoal". Getbusinessname "(" Ptypeno "varchar)
RETURNS "Pg_catalog". VarChar "as $BODY $

BEGIN
Set search_path= ' Digoal ';

Return (select TypeName from Tbl_core_bus where typeno= Ptypeno);

End;

$BODY $
LANGUAGE ' Plpgsql ' VOLATILE;