Обсуждение: ODBC options
I was trying to figure how to set the log directory on my Windows client using a parameter in the connection string. Short answer is that you can't.
But you can using the DSN settings.
I was under the impression that going DSN-less and using the parameters on the connection string, we were free from the effects of the DSN settings.
That's not true.
The DSN settings become the default values, overridden by anything in the connection string.
Conversely, there are two settings which, for reasons that escape me, can't be set via the connection string.
In the process, I found the documentation on the ODBC settings to be scattered and inconsistent, so I documented all the settings and their abbreviations in a spreadsheet. Is there a useful place or format where I can post that?
Spreadsheet is here if anyone wants to look at it.
Thanks,
Brad.
Brad.
On Tue, Oct 3, 2023 at 05:34:01PM -0500, Brad White wrote: > I was trying to figure how to set the log directory on my Windows client > using a parameter in the connection string. Short answer is that you can't. > But you can using the DSN settings. > I was under the impression that going DSN-less and using the parameters on the > connection string, we were free from the effects of the DSN settings. > That's not true. > The DSN settings become the default values, overridden by anything in the > connection string. > Conversely, there are two settings which, for reasons that escape me, can't be > set via the connection string. > In the process, I found the documentation on the ODBC settings to be scattered > and inconsistent, so I documented all the settings and their abbreviations in a > spreadsheet. Is there a useful place or format where I can post that? > > Spreadsheet is here if anyone wants to look at it. > https://www.dropbox.com/scl/fi/v1uj1umtj20k1ljenodvy/ > psqlODBC-Configuration-Options.xlsx?rlkey=fit9kbgy0fv0fr9vt0u0a9oim&dl=0 Please see: https://odbc.postgresql.org/faq.html#1.6 -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
The PL/pgSQL page on triggers ( https://www.postgresql.org/docs/16/plpgsql-trigger.html ) contains the following example (I'm excerpting only the essential parts here):
Example 43.4. A PL/pgSQL Trigger Function for Auditing
...
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL, -- <= COLUMN IN QUESTION
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- making use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; -- <= ARGUMENT IN QUESTION
-- similar code with same issue follows for the other TG_OPs...
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL, -- <= COLUMN IN QUESTION
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- making use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; -- <= ARGUMENT IN QUESTION
-- similar code with same issue follows for the other TG_OPs...
The emp_audit table has a column named 'userid', which in actual usage (next-to-last line quoted) is populated by 'user' which seems undefined in the context. Was that intended to be 'current_user', or am I missing something?
On Sat, Oct 7, 2023 at 11:11 AM Kirk Parker <khp@equatoria.us> wrote:
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; -- <= ARGUMENT IN QUESTION
The emp_audit table has a column named 'userid', which in actual usage (next-to-last line quoted) is populated by 'user' which seems undefined in the context. Was that intended to be 'current_user', or am I missing something?
user is a valid pseudo-function:
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Sat, Oct 7, 2023 at 11:11 AM Kirk Parker <khp@equatoria.us> wrote: >> INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; -- <= ARGUMENT IN QUESTION >> The emp_audit table has a column named 'userid', which in actual usage >> (next-to-last line quoted) is populated by 'user' which seems undefined in >> the context. Was that intended to be 'current_user', or am I missing >> something? > user is a valid pseudo-function: > https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-SESSION Yeah, either way has the same result. However, I wonder if we should change this example to use current_user for clarity. It does look more like it's intended to be a variable or column reference than a built-in function. regards, tom lane
On Sat, Oct 7, 2023 at 1:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sat, Oct 7, 2023 at 11:11 AM Kirk Parker <khp@equatoria.us> wrote:
>> INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; -- <= ARGUMENT IN QUESTION
>> The emp_audit table has a column named 'userid', which in actual usage
>> (next-to-last line quoted) is populated by 'user' which seems undefined in
>> the context. Was that intended to be 'current_user', or am I missing
>> something?
> user is a valid pseudo-function:
> https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-SESSION
Yeah, either way has the same result. However, I wonder if we should
change this example to use current_user for clarity. It does look
more like it's intended to be a variable or column reference than
a built-in function.
Since the previous example on the page uses 'current_user' (which I suppose is why I didn't look further to see if 'user' was also a function), perhaps that would be a good idea.
> On 7 Oct 2023, at 22:22, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "David G. Johnston" <david.g.johnston@gmail.com> writes: >> On Sat, Oct 7, 2023 at 11:11 AM Kirk Parker <khp@equatoria.us> wrote: >>> INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; -- <= ARGUMENT IN QUESTION >>> The emp_audit table has a column named 'userid', which in actual usage >>> (next-to-last line quoted) is populated by 'user' which seems undefined in >>> the context. Was that intended to be 'current_user', or am I missing >>> something? > >> user is a valid pseudo-function: >> https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-SESSION > > Yeah, either way has the same result. However, I wonder if we should > change this example to use current_user for clarity. It does look > more like it's intended to be a variable or column reference than > a built-in function. Agreed, and "user" is a hard search term to use for discovering what it is. +1 for changing to current_user. -- Daniel Gustafsson
Daniel Gustafsson <daniel@yesql.se> writes:
>> On 7 Oct 2023, at 22:22, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, either way has the same result. However, I wonder if we should
>> change this example to use current_user for clarity. It does look
>> more like it's intended to be a variable or column reference than
>> a built-in function.
> Agreed, and "user" is a hard search term to use for discovering what it is. +1
> for changing to current_user.
OK, I'll take care of this later today.
regards, tom lane
On Fri, Oct 6, 2023 at 2:30 PM Bruce Momjian <bruce@momjian.us> wrote:
On Tue, Oct 3, 2023 at 05:34:01PM -0500, Brad White wrote:
> I was trying to figure how to set the log directory on my Windows client
> using a parameter in the connection string. Short answer is that you can't.
> But you can using the DSN settings.
> I was under the impression that going DSN-less and using the parameters on the
> connection string, we were free from the effects of the DSN settings.
> That's not true.
> The DSN settings become the default values, overridden by anything in the
> connection string.
> Conversely, there are two settings which, for reasons that escape me, can't be
> set via the connection string.
> In the process, I found the documentation on the ODBC settings to be scattered
> and inconsistent, so I documented all the settings and their abbreviations in a
> spreadsheet. Is there a useful place or format where I can post that?
>
> Spreadsheet is here if anyone wants to look at it.
> https://www.dropbox.com/scl/fi/v1uj1umtj20k1ljenodvy/
> psqlODBC-Configuration-Options.xlsx?rlkey=fit9kbgy0fv0fr9vt0u0a9oim&dl=0
Please see:
https://odbc.postgresql.org/faq.html#1.6
I'm confused.
I see that it says to send bug reports to the odbc mailing list.
This isn't a bug report, but I did already send a shortened version there.
Was there something else you wanted to see?
Happy to provide any additional documentation if there is something that I missed.
Thanks,
Brad.