Re: Loading current_user and current_timestamp using COPY

Herb,

> I'm trying to load the current_user and current timestamp (as an
> audit)
> in a set of data using the COPY FROM... statement.
> I cannot get the session_user, current_user or user to give me
> anything
> but the text itself...I even tried the obsolete getpgusername() and
> thats exactly what it entered!
>
> To get the current_timestamp, I had to use the now() function.

Hmmm ... I was under the impression that you could not use any builtin
functions with COPY. Just literals. I'm surprised that NOW() worked.

Just in case, I'm cross-posting your question to the SQL list.

Couple of suggestions for immediate workarounds:
1. If the data sets you are importing are small, use INSERT statements
instead of COPY.
2. If you are importing large amounts of data, do the following:
2.1. Modify your table so that the UpdateUserID and UpdateTimestamp
columns are last.
2.2. Set the defaults on those columns to current_user and
current-timestamp.
2.3. COPY all columns except those two. They should populate
according to the defaults (I hope).

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco