On 1/3/25 15:22, Jan Behrens wrote:
On Fri, 3 Jan 2025 13:56:02 -0800
Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.
Is 'some_schema' a known item when installing?
Once you have the search_path defined and assuming all the objects you
want are in that path, then yes you can drop the schema qualification.
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.
If you are doing this as an extension then I suspect you want the
processes shown here:
https://www.postgresql.org/docs/17/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION
Kind Regards,
Jan Behrens
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
