Re: search_path for PL/pgSQL functions partially cached?

On Fri, 3 Jan 2025 13:56:02 -0800
Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:

> At this point I am lost as to what the overall goal of this is.
>
> Can you provide a 10000 ft view if what it is you are trying to achieve?

Sure! I would like to create a component (e.g. a PostgreSQL extension)
that provides a function which processes some complex data, without
making any requirements regarding where the data is stored. To pass
this data to the function, I could use arrays of composite types, but
that seems to be very bulky. Another option would be to use cursors,
but that didn't turn out to work very smooth either.

Instead, I plan to expect the function to receive a query string that
will get the data that is being processed by the function.

That query string should be allowed to refer to tables in the
search_path at the caller's side.

Therefore, I cannot use the "SET search_path FROM CURRENT" in my
"CREATE FUNCTION" statement, because it would overwrite the current
search_path on each call of the function.

Thus my idea is to do this (simplified):

CREATE FUNCTION "some_function" ("query_p" TEXT) RETURNS "some_type"

--------------------------------------------------------------------
-- I cannot use SET search_path FROM CURRENT here, because "query_p"
-- shall refer to tables in the search_path of the caller.
--------------------------------------------------------------------

LANGUAGE plpgsql AS $$
DECLARE
"old_search_path" TEXT;

----------------------------------------------------------------
-- I have to fully qualify types in the DECLARE section.
----------------------------------------------------------------

"some_variable" "some_schema"."some_type";
BEGIN
SELECT current_setting('search_path') INTO "old_search_path";
PERFORM set_config(
'search_path',
'some_schema, pg_temp, ' || "old_search_path",
TRUE
);

----------------------------------------------------------------
-- Do I have to fully qualify types and operators from
-- "myschema" here? Or is it safe to not fully qualify them?
----------------------------------------------------------------
END;
$$;

That is my overall idea.

My problem is that I'm confused about WHEN EXACTLY I have to qualify
tables/types, etc. It is very hard to understand from reading (just) the
documentation.

Kind Regards,
Jan Behrens