DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=PTcAXUuSQMSp/8VyfQ0zHpBw99mA1z31aDIZtLPoMAVdgnRAnufomuU1qLEj4gxMVS
	L+rFF1TokXTnG/+hrf7Y9ZkjrXzoLJj7DX9w6gaG1aclMG1ootaM/RIWO1202cNOIWgP
	NA1L1R0ol/M54e+cs/7kS3wnHmjm2b30d+gvw=
MIME-Version: 1.0
Date: Mon, 29 Jun 2009 23:47:42 -0700
Message-ID: <11c0133d0906292347q4c65cfe2m7b658232d0b13a9f@mail.gmail.com>
Subject: Recording how a table is used
From: Daniel Gordon <dragongordon@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=00163631059def398d046d8b2eb0

--00163631059def398d046d8b2eb0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

I'm trying to record the results of a select statement into a separate
table.  I need the information selected, the column it was stored in, the
table it was stored in, and the query that selected it.  Everything I've
read so far says that triggers can't trigger from select statements, and
they don't have a variable that stores the query it triggered from.  I would
be happy with partial solutions since my other option is a complicated perl
script that has to parse the query statement to figure out where it is
looking for the information

Also, my next step is to do the same for as many types of statements as
possible, so if anyone knows how to retrieve the insert, update, or delete
statement sent to the database from inside the trigger, that would be useful
as well.

Here is the table I'm trying to fill, in case it is useful

create table sql_query_data_log (
 id serial,
 create_ time timestamp DEFAULT now(),
 query varchar,
 table text,
 column text,
 data varchar
 );

--00163631059def398d046d8b2eb0
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I&#39;m trying to record the results of a select statement into a separate =
table.=A0 I need the information selected, the column it was stored in, the=
 table it was stored in, and the query that selected it.=A0 Everything I=
9;ve read so far says that triggers can&#39;t trigger from select statement=
s, and they don&#39;t have a variable that stores the query it triggered fr=
om.=A0 I would be happy with partial solutions since my other option is a c=
omplicated perl script that has to parse the query statement to figure out =
where it is looking for the information<br>
<br>Also, my next step is to do the same for as many types of statements as=
 possible, so if anyone knows how to retrieve the insert, update, or delete=
 statement sent to the database from inside the trigger, that would be usef=
ul as well.<br>
<br>Here is the table I&#39;m trying to fill, in case it is useful<br><pre>=
create table sql_query_data_log (<br> id serial,<br> create_ time timestamp=
 DEFAULT now(),<br> query varchar,<br> table text,<br> column text,<br>
 data varchar<br> );</pre><br>

--00163631059def398d046d8b2eb0--