public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jan Behrens <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: search_path for PL/pgSQL functions partially cached?
Date: Sat, 4 Jan 2025 00:22:03 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAKFQuwaU19_6HaB+9-L-fQhjUr8_5ACvxLAPRBhEdfLv9JVZBg@mail.gmail.com>
<[email protected]>
<CAKFQuwZdt+YLi=9_WraRLajuOkmw4esFzbHTXmv5MwHJemdDhQ@mail.gmail.com>
<[email protected]>
<[email protected]>
On Fri, 3 Jan 2025 13:56:02 -0800
Adrian Klaver <[email protected]> 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
view thread (33+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: search_path for PL/pgSQL functions partially cached?
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox