How to identify which schema calls a function in another database without schema? I have a database with 3 schemas (db1) where each schema calls the “guardarTransaccion” function of a database without schema (db2).

When calling the “guardarTransaccion” function pass the record id and the db2 has to connect to the corresponding schema using DBLINK and load the detail data (eg db1.schema1).

The problem I have is when we call the function from a schema in db1 I dont have a way to identify which schema I called the function for to load the corresponding data.

The code of the function in DB2 is:

CREATE OR REPLACE FUNCTION "public"."guardarTransaccion"("tipo" text, "idRemito" int4, "idPicking" int4)
  RETURNS "pg_catalog"."int4" AS $BODY$
DECLARE numeroRemito TEXT;
BEGIN
SET search_path = 'public', 'public', 'pg_catalog';


    -- load db1 data using dblink view
    IF "tipo" = 'E' THEN
        UPDATE picking SET rom_id = "idRemito", pk_remitado = 1, pk_cerrado = 1 WHERE pk_id = "idPicking";
        SELECT rmv_numero_comprobante INTO numeroRemito FROM romaneos_venta WHERE rmv_id = "idRemito";
    ELSE 
        UPDATE picking SET rmv_id = "idRemito", pk_remitado = 1, pk_cerrado = 1 WHERE pk_id = "idPicking";
        SELECT rmv_numero_comprobante INTO numeroRemito FROM remitos_venta WHERE rmv_id = "idRemito";
    END IF;

    -- operaciones con los datos / Operations with data


RETURN 1;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;


The way we find it to solve it is to create schema in the db2 only with the changes to each db1 schema, but it does not seem correct, what would be the best way to solve this?
You can get the current schema by using the System Information Functions


current_schema[()] returns text: name of current schema
current_schemas(boolean) returns text[]: names of schemas in search path, optionally including implicit schemas


I don't think dblink passes the context of the functions executed from the original database. So you'll have to wrap those functions and explicitly pass the context, perhaps by making the function variadic

CREATE OR REPLACE FUNCTION public.guardarTransaccion("tipo" text, "idRemito" int4, "idPicking" int4,
  context DEFAULT '{}'::text[] )


Then call it like the context explicitly. I would probably just think of a better way to engineer the system.