Re: [ADMIN] [SQL] parsing audit table
Hi Jamie & All I experimented a bit with the audit function and i added a field to the audit table where i store which table the audit record originates from. I was thinking it should be possible to to pare the hstore fields using populate_record to its original form using the original table definition. The query below works but of course only as long as there is only one record select * FROM populate_record(null::t, (select log_new_values FROM audit.audit_log WHERE log_schema = 'public' AND log_table = 't' AND log_id = 10 )); What i would like to do is to return a goup of records for example all record within a time span for a certain table and my sql understanding is not good enough to solve this and i cant really find any examples how it should be done. The query below gets audit records within a times pan but it fails since more then one record is returned. select * FROM populate_record(null::t, (select log_new_values FROM audit.audit_log WHERE log_schema = 'public' AND log_table = 't' AND log_when BETWEEN '2011-08-20' AND '2011-08-25' )); Is what i like to to at all possible to solve ? Thank you in Advance! Jan Eskilsson 2011/8/17 Jaime Casanova > On Tue, Aug 16, 2011 at 4:02 PM, M. D. wrote: > > Hi everyone, > > > > I'm a bit lazy, or actually in a bit of a crunch. I added an audit > > recording a few months ago, but never really used it much, but today I'm > > seeing a bunch of suspicious activity by one user. Does someone have any > > function to quickly parse this data? > > > > that's not exactly auditable information :D > > i made this one for that: https://github.com/jcasanov/pg_audit > > it has two versions one using hstore in which you will see in old > "column=>old_data" and in new "column=>new_data" seems better and i > guess you can use hstore functions on it > (http://www.postgresql.org/docs/9.0/static/hstore.html) > > The other one uses arrays to store column names, old values, new > values, still more parseable > > -- > Jaime Casanova www.2ndQuadrant.com > Professional PostgreSQL: Soporte 24x7 y capacitación > > -- > Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
[SQL] hstore and populate_record
Hi All Background, I have a table that i need to audit changes for tracability etc. I found an example on one of the postgres lists to use hstore:s to store the audit data. I find this to be a really good idea since its really simple to implement and it works really well. But now comes next step, how to get the data back from the hstore into a record in the original format. My two tables looks like this, first the table to be audited, CREATE TABLE commissionbase ( commissionbase_pkey integer NOT NULL, salesorder character varying(20) NOT NULL, salesmanid character varying(50) NOT NULL, stockitem character varying(50) NOT NULL, quantity double precision, price numeric(15,2), commission character varying(10), commissionsum numeric(15,2), CONSTRAINT commissionbase_pkey PRIMARY KEY (commissionbase_pkey ), ) And then the audit table, CREATE TABLE audit.audit_log ( log_operation text, log_old_values hstore, log_new_values hstore, log_table text, CONSTRAINT audit_log_pkey PRIMARY KEY (log_id ) ) My problem, SELECT * FROM public.populate_record(null::commissionbase, (select log_new_values from audit.audit_log where log_table = 'commissionbase' LIMIT 1)); This select works really well as long as i limit it to one record, when i remove the limit restriction it dont work because the subquery returns more then one record. Is it possible to rewrite to handle multiple records, my sql knowledge is a bit to limited to figure out how? All input is greatly apreciated ! Thank you in advance ! Best Regards Jan Eskilsson -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. Please consider the environment before you print this email. (1) The contents of this transmission are privileged and confidential and intended solely for the use of the addressee. Any disclosure, distribution or copying of the contents, other than by the addressee, is strictly prohibited. If you receive this transmission in error, please notify us immediately and destroy the material received. (2) All incoming and outgoing emails and any attachments are subjected to a virus scanner and are believed to be free of any virus, or any other defect which might affect any computer or IT system into which they are received and opened. Therefore, it is the responsibility of the recipient to ensure that they are virus free and no responsibility is accepted by Jan Eskilsson for any loss or damage arising in any way from receipt or use thereof. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to use hstore
Hi all I log data in a table using a hstore field. My problem is that i would like to be able to retrieve a group of records from the hstore table and present them in a grid so I would like to convert the hstore records back to the original table format. In the manual i found an example how to convert to a predefined record type, exactly what i like to do but the example works great as long as there is only one record to convert. SELECT * FROM populate_record(null::test, '"col1"=>"456", "col2"=>"zzz"');. But i like to to something like this SELECT * FROM populate_record(null::test, (select logfield from logtable where '"col1"=>"456" )); So i wonder how should i do if i have lots of hstore records to convert ? My SQl knowledge is a bit short :-) Thank you in advance! Jan Eskilsson -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. Please consider the environment before you print this email. (1) The contents of this transmission are privileged and confidential and intended solely for the use of the addressee. Any disclosure, distribution or copying of the contents, other than by the addressee, is strictly prohibited. If you receive this transmission in error, please notify us immediately and destroy the material received. (2) All incoming and outgoing emails and any attachments are subjected to a virus scanner and are believed to be free of any virus, or any other defect which might affect any computer or IT system into which they are received and opened. Therefore, it is the responsibility of the recipient to ensure that they are virus free and no responsibility is accepted by Jan Eskilsson for any loss or damage arising in any way from receipt or use thereof. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to use hstore
Hi Sergey Thats exacly what i was looking for, thanks a million ! Best regards Jan Eskilsson 2012/6/5 Sergey Konoplev : > On Mon, Jun 4, 2012 at 4:08 PM, Jan Eskilsson wrote: >> My problem is that i would like to be able to retrieve a group of >> records from the hstore table and present them in a grid so I would >> like to convert the hstore records back to the original table format. >> In the manual i found an example how to convert to a predefined record >> type, exactly what i like to do but the example works great as long as >> there is only one record to convert. > > I think this will answer your question: > > grayhemp=# \d t > Table "public.t" > Column | Type | Modifiers > +-+--- > i | integer | > t | text | > h | hstore | > > grayhemp=# select * from t; > i | t | h > ---+-+ > 1 | aaa | > 2 | bbb | "a"=>"1", "b"=>"2" > 3 | ccc | "a"=>"1", "c"=>"3" > (3 rows) > > grayhemp=# \d r > Table "public.r" > Column | Type | Modifiers > +-+--- > a | integer | > b | integer | > c | integer | > > grayhemp=# select (populate_record(null::r, h)).* from t; > a | b | c > ---+---+--- > | | > 1 | 2 | > 1 | | 3 > (3 rows) > >> >> SELECT * FROM populate_record(null::test, >> '"col1"=>"456", "col2"=>"zzz"');. >> >> But i like to to something like this >> SELECT * FROM populate_record(null::test, >> (select logfield from logtable where >> '"col1"=>"456" )); >> >> >> So i wonder how should i do if i have lots of hstore records to >> convert ? My SQl knowledge is a bit short :-) >> >> >> Thank you in advance! >> >> >> Jan Eskilsson >> >> -- >> No trees were killed in the creation of this message. >> However, many electrons were terribly inconvenienced. >> >> Please consider the environment before you print this email. >> >> (1) The contents of this transmission are privileged and confidential >> and intended solely for the use of the addressee. Any disclosure, >> distribution or copying of the contents, other than by the addressee, >> is strictly prohibited. If you receive this transmission in error, >> please notify us immediately and destroy the material received. >> (2) All incoming and outgoing emails and any attachments are subjected >> to a virus scanner and are believed to be free of any virus, or any >> other defect which might affect any computer or IT system into which >> they are received and opened. Therefore, it is the responsibility of >> the recipient to ensure that they are virus free and no responsibility >> is accepted by Jan Eskilsson for any loss or damage arising in any >> way from receipt or use thereof. >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > > > -- > Sergey Konoplev > > a database and software architect > http://www.linkedin.com/in/grayhemp > > Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204 -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. Please consider the environment before you print this email. (1) The contents of this transmission are privileged and confidential and intended solely for the use of the addressee. Any disclosure, distribution or copying of the contents, other than by the addressee, is strictly prohibited. If you receive this transmission in error, please notify us immediately and destroy the material received. (2) All incoming and outgoing emails and any attachments are subjected to a virus scanner and are believed to be free of any virus, or any other defect which might affect any computer or IT system into which they are received and opened. Therefore, it is the responsibility of the recipient to ensure that they are virus free and no responsibility is accepted by Jan Eskilsson for any loss or damage arising in any way from receipt or use thereof. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
