I have a simple stored procedure whose return value depends on the value of inet_client_addr(). How can I override inet_client_addr() for the purpose of unit tests when testing my stored procedure?

The only solution I've come up with so far is to create a wrapper function around inet_client_addr():

CREATE FUNCTION my_inet_client_addr() RETURNS INET AS $$
    SELECT inet_client_addr();
$$ LANGUAGE sql;

Then use that in my function:

CREATE local_connection() RETURNS BOOLEAN AS $$
    SELECT my_inet_client_addr() = '';
$$ LANGUAGE sql;

Then in my unit test, I can re-define my_inet_client_addr():

REPLACE FUNCTION my_inet_client_addr() RETURNS INET AS $$
    SELECT ''::INET;
$$ LANGUAGE sql;

is(local_connection(),TRUE,'Connection from is local');

REPLACE FUNCTION my_inet_client_addr() RETURNS INET AS $$
    SELECT ''::INET;
$$ LANGUAGE sql;

is(local_connection(),FALSE,'Connection from is not local');


Is there any way to accomplish the same without the wrapper function my_inet_client_addr()?

  • inject the dependency? your_procedure( inet_client_addr() ) instead of an implicit call to it, no mocking/overriding required. Jul 8, 2014 at 21:07
  • @xenoterracide: That could work... although it would certainly be annoying, and would touch a lot of code.
    – Flimzy
    Jul 8, 2014 at 21:08
  • I forget if in pgsql whether or not the signature is super important, but you could also do it as an optional parameter so if it's passed in it uses the passed in value, if not it uses inet_client_addr(), or if signatures are highly important have the sig without the extra param, delegate to the one with. Either way preserves the existing API while allowing testing of core logic. Jul 8, 2014 at 21:12
  • @xenoterracide: Signatures are important, but I could then create a local_connection(INET) function (which takes the argument), which is in turn wrapped by a local_connection() function which passes inet_client_addr(). This is probably a cleaner method, which would preserve the existing API.
    – Flimzy
    Jul 8, 2014 at 21:24

2 Answers 2


inet_client_addr() is a system information function.
It is located in the schema pg_catalog like other built-in functions (except for additional modules).

pg_catalog is automatically part of the search_path. Per documentation:

In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data types, functions, and operators. pg_catalog is always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched before searching the path's schemas. This ensures that built-in names will always be findable. However, you can explicitly place pg_catalog at the end of your search path if you prefer to have user-defined names override built-in names.

Bold emphasis mine.
So we create a dedicated schema and place it before pg_catalog in the search_path:


CREATE OR REPLACE FUNCTION override.inet_client_addr()
SELECT ''::inet

SET search_path = override, pg_catalog, public;

Then your call finds your custom override-function first:

SELECT inet_client_addr();

db<>fiddle here

Make sure, unprivileged users cannot create objects in the override schema, or they can play all kinds of tricks on you. That's not the case by default. Per documentation:

No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces.

Bold emphasis mine.

Care is needed if the same user should be allowed to create objects in the database.
Per documentation:

The first schema named in the search path is called the current schema. Aside from being the first schema searched, it is also the schema in which new tables will be created if the CREATE TABLE command does not specify a schema name.

Always specify a schema name for CREATE commands, and disallow creating object to all by default to rule out mistakes.

  • 1
    darnit, I tried that but used custom, public instead of custom, pg_catalog, public and it didnt work :) Good answer. Jul 8, 2014 at 23:08
  • @NeilMcGuigan: You were on the right track. +1 for your answer. Jul 8, 2014 at 23:09
  • I tried Neil's approach, too... explicitly specifying pg_catalog in the search path was the missing piece. Thanks!
    – Flimzy
    Jul 9, 2014 at 15:28

Must be postgres user or other Superuser for mine. Only need create schema privilege for Erwin's answer.

start tests:

alter function inet_client_addr() rename to _inet_client_addr;

create function inet_client_addr() returns inet language sql stable cost 1 as '
  select ''''::inet;

select inet_client_addr();

end tests

alter function inet_client_addr() rename to inet_client_addr_mock;

alter function _inet_client_adds() rename to inet_client_addr;

--the regular function:
select inet_client_addr();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.