How can I fake inet_client_addr() for unit tests in PostgreSQL?

Question

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() = '127.0.0.1';
$$ LANGUAGE sql;

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

BEGIN;
SELECT PLAN(2);
REPLACE FUNCTION my_inet_client_addr() RETURNS INET AS $$
 SELECT '127.0.0.1'::INET;
$$ LANGUAGE sql;
is(local_connection(),TRUE,'Connection from 127.0.0.1 is local');
REPLACE FUNCTION my_inet_client_addr() RETURNS INET AS $$
 SELECT '192.168.1.1'::INET;
$$ LANGUAGE sql;
is(local_connection(),FALSE,'Connection from 192.168.1.1. is not local');
ROLLBACK;

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

• xenoterracide: inject the dependency? your_procedure( inet_client_addr() ) instead of an implicit call to it, no mocking/overriding required.
• Flimzy: @xenoterracide: That could work... although it would certainly be annoying, and would touch a lot of code.
• xenoterracide: 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.
• Flimzy: @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.

Answer 1

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 SCHEMA override;
CREATE OR REPLACE FUNCTION override.inet_client_addr()
 RETURNS inet LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT '127.0.0.1'::inet
$func$;
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.

• Neil McGuigan: darnit, I tried that but used custom, public instead of custom, pg_catalog, public and it didnt work :) Good answer.
• Erwin Brandstetter: @NeilMcGuigan: You were on the right track. +1 for your answer.
• Flimzy: I tried Neil's approach, too... explicitly specifying pg_catalog in the search path was the missing piece. Thanks!

Answer 2

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 ''192.168.0.1''::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();