Thread: Generating History

Is there any easy method for getting a hold of the OID and XID for a
given row within a plpgsql (or another type) of function?

Statements like NEW.oid appear to fail, and xid isn't to be found.
I'd like to use these to create the history of an application which
can be rolled back (by the application) which is stored in another
table for easy stats generation.

The reason for having the database do the work is that we want to keep
the actions of the DBA's available to our clients to scrutinize,
otherwise the wrappers could do the extra required dirty work to
accomplish this.

Is there an easier way of doing this?  Since there's no environment
variable type entity (variables common for an entire transaction) I
was wondering if a cursor can flow between multiple functions?
Each function uses FETCH -1 FROM transaction_id; to get information.
Passing the transaction_id as a parameter isn't really an option as
most of the functions() would be run via automated triggers.

BEGIN WORK;
DECLARE transaction_id CURSOR FOR SELECT 8329;
FETCH 1 FROM transaction_id;

select function1();
select function2();
select function3();

CLOSE transaction_id;
COMMIT WORK;

Thanks for your help in advance.

--
Rod Taylor

There are always four sides to every story: your side, their side, the
truth, and what really happened.

"Rod Taylor" <rod.taylor@inquent.com> writes:
> Is there any easy method for getting a hold of the OID and XID for a
> given row within a plpgsql (or another type) of function?

> Statements like NEW.oid appear to fail, and xid isn't to be found.

The reason this doesn't work in plpgsql is that the underlying "SPI"
tuple-access routines don't support access to the system attributes
of a tuple.  It'd be relatively straightforward to extend them to
do so, if someone cared to work on that.

I thought you could do it in an SQL-language function, but that doesn't
seem to work either, for reasons that may be strictly historical at
this point --- the relevant error message is coming out of this:
   attno = get_attnum(relid, attname);
   /* XXX Is there still a reason for this restriction? */   if (attno < 0)       elog(ERROR, "Cannot reference
attribute'%s'"            " of tuple params/return values for functions", attname);
 

Think I'll look and see if this restriction could be dropped now.

For 7.0.* though, it seems the only way to get this data in a PL
function is to write a C-language function to extract it ...
        regards, tom lane