Re: [SQL] Start up question about triggers
Sorry This is the complete message Hello all, I know that this question may be really simple, but I have decided to ask here due to fact that I don't know how to search for this on google or on the docs. I created a trigger fuction which updates a specific row in some table A. Is it possible to retain the query that was used to trigger the function. For example Table A query_row_id query_row TABLE B id name If I create a trigger on table B that says that after the insert command to write the query into table A. So if I do insert into B values(1,"Blah") this will trigger my trigger. Is there any way to get the "insert into B values(1,"Blah")? At the moment I can see only the type of query that it is (INSERT UPDATE DELETE) best regards, Fotis From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Forums @ ExistanzeSent: 22 June 2006 12:19To: pgsql-sql@postgresql.orgSubject: [SQL] Start up question about triggers Hello all, I know that this question may be really simple, but I have decided to ask here due to fact that I don't know how to search for this on google or on the docs. I created a trigger fuction which updates a specific row in some table A. Is it possible to retain the query that was used to trigger the function. For example Table A query_row_id query_row TABLE B id name
Re: [SQL] Start up question about triggers
Why not just create a history table and have the trigger copy the data out of the table into the history table with a time stamp of the change. Then you don't need the query.For exampleTable Aa_id,a_value1, a_value2Table A_hista_id,a_dt,a_value1,a_value2Then A_hist has a PK of a_id, a_dtThis would also be a lot easier to see WHAT changed and WHEN. You can use the NEW.col and OLD.col to see the new and old values during inserts and updates.Of course, I don't know your need so this may not be achieving your goal.-Aaron BonoOn 6/22/06, Forums @ Existanze [EMAIL PROTECTED] wrote: Sorry This is the complete message Hello all, I know that this question may be really simple, but I have decided to ask here due to fact that I don't know how to search for this on google or on the docs. I created a trigger fuction which updates a specific row in some table A. Is it possible to retain the query that was used to trigger the function. For example Table A query_row_id query_row TABLE B id name If I create a trigger on table B that says that after the insert command to write the query into table A. So if I do insert into B values(1,Blah) this will trigger my trigger. Is there any way to get the insert into B values(1,Blah)? At the moment I can see only the type of query that it is (INSERT UPDATE DELETE) best regards, Fotis From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Forums @ ExistanzeSent: 22 June 2006 12:19To: pgsql-sql@postgresql.orgSubject: [SQL] Start up question about triggers Hello all, I know that this question may be really simple, but I have decided to ask here due to fact that I don't know how to search for this on google or on the docs. I created a trigger fuction which updates a specific row in some table A. Is it possible to retain the query that was used to trigger the function. For example Table A query_row_id query_row TABLE B id name
Re: [SQL] Start up question about triggers
Thank you for your answer, We had though about your solution, the problem is that we have around 80 tables at the moment so your method would suggest adding another 80. I was wondering if it was possible to retrieve the query in the trigger function, cause what we wanted to achieve was to trigger a query log when any insert or update or delete operation was made on any of the 80 tables. This way we would have something like a query log table. Which will have the queries in the order that they were executed by n number of clients. Say one client updates a row, and the next client deletes it, we want to know the queries that occurred in that particular order. I hope this makes some sense :-) I should also mention that what we are trying to achieve is some sort of partial backup operation. Because of bad initial design, we didn't foresee this comming. So now we have two options, changing all the tables,queries and code,to contain two timestamps columns representingcreated and updated row, a flag for deleted row, and have some sort of maintanance operation that will clean all the deleted records, and create insert/update statements for those records that have been updated ie( time_of_update time_of_creation). This will give us a list of operation (INSERT OR UPDATE statements) that can be written to a file, and run from a file. So if I had 10 partiall backups and ran them sequencially I would in theory have the data that I originally had. At the moment we are doing full back using pgdump, but this is another type of requirement. Any ideas greatly appreciated. Best Regards, Fotis From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Aaron BonoSent: 22 June 2006 19:03To: Forums @ ExistanzeCc: pgsql-sql@postgresql.orgSubject: Re: [SQL] Start up question about triggers Why not just create a history table and have the trigger copy the data out of the table into the history table with a time stamp of the change. Then you don't need the query.For exampleTable Aa_id,a_value1, a_value2Table A_hista_id,a_dt,a_value1,a_value2Then A_hist has a PK of a_id, a_dtThis would also be a lot easier to see WHAT changed and WHEN. You can use the NEW.col and OLD.col to see the new and old values during inserts and updates.Of course, I don't know your need so this may not be achieving your goal.-Aaron Bono On 6/22/06, Forums @ Existanze [EMAIL PROTECTED] wrote: Sorry This is the complete message Hello all, I know that this question may be really simple, but I have decided to ask here due to fact that I don't know how to search for this on google or on the docs. I created a trigger fuction which updates a specific row in some table A. Is it possible to retain the query that was used to trigger the function. For example Table A query_row_id query_row TABLE B id name If I create a trigger on table B that says that after the insert command to write the query into table A. So if I do insert into B values(1,"Blah") this will trigger my trigger. Is there any way to get the "insert into B values(1,"Blah")? At the moment I can see only the type of query that it is (INSERT UPDATE DELETE) best regards, Fotis From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Forums @ ExistanzeSent: 22 June 2006 12:19To: pgsql-sql@postgresql.orgSubject: [SQL] Start up question about triggers Hello all, I know that this question may be really simple, but I have decided to ask here due to fact that I don't know how to search for this on google or on the docs. I created a trigger fuction which updates a specific row in some table A. Is it possible to retain the query that was used to trigger the function. For example Table A query_row_id query_row TABLE B id name
Re: [SQL] start
You say that you're running Suse 8. You can also start the process with that Yast. Start Yast2 - System - Runlevel Editor- and then you can select the runlevel when you want that postgre starts. I start it at runlevel 3. If you installed postgre it manually you have a little work to do, probably you will have to make your own start script. In my situation(also manual installation) I just copied the script which came with suse and edited it for my configuration... it worked for me. Good luck! - Original Message - From: H.J. Sanders [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 03, 2004 10:36 PM Subject: [SQL] start Hello list. I have difficulties starting the postmaster automatically at boot time (everything I tried is done by 'root'). Can someone give me an example for LINUX (SUSE 8). Many thanks. Henk Sanders ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] start
H.J. Sanders wrote: I have difficulties starting the postmaster automatically at boot time (everything I tried is done by 'root'). Can someone give me an example for LINUX (SUSE 8). Maybe you would rather want to download the binary packages, which take care of that. RPMs for SuSE are available on the PostgreSQL mirrors in the directory binary. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Start-up problems
Look at /etc/ beyaRecords - The home Urban music wrote: Kaloyan, thanks for reply. Still finding my way around the unix environment so could you tell me where the file you mention is situated? On 8 Jan 2004, at 13:24, Kaloyan Iliev Iliev wrote: This is what I have in my rc.local on FreeBSD: su pgsql -c /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -o -i -l /usr/local/pgsql/logfile start beyaRecords - The home Urban music wrote: I am running postgresql 7.4.1 on OS X 10.3 and am having to manually start-up postgresql using /usr/local/pgsql/bin/postmaster -i -D /usr/local/pgsql/data. Is there a script available which will enable me to automate this process, so that postgres loads up at startup? Uzo ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Start and End Day of a Week
Hi,
I've found my own answer... this is in case somebody needs it someday,
it seems that works properly in PG 7.3, one function gives the start of
the week and the other one the end:
CREATE OR REPLACE FUNCTION weekstart(int4,int4)
RETURNS TIMESTAMP
AS'
DECLARE
year ALIAS FOR $1;
week ALIAS FOR $2;
startsin TIMESTAMP;
BEGIN
SELECT INTO startsin
(SELECT CAST(year || ''-01-01'' AS TIMESTAMP) -
(date_part(''dow'',CAST(year || ''-01-01'' AS
TIMESTAMP))||'' days'')::INTERVAL) +
((week*7)||'' days'')::INTERVAL - ''7
days''::INTERVAL;
RETURN startsin;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION weekend(int4,int4)
RETURNS TIMESTAMP
AS'
DECLARE
year ALIAS FOR $1;
week ALIAS FOR $2;
endsin TIMESTAMP;
BEGIN
SELECT INTO endsin
(SELECT CAST(year || ''-01-01'' AS TIMESTAMP) -
(date_part(''dow'',CAST(year || ''-01-01'' AS TIMESTAMP))||''
days'')::INTERVAL) +
((week*7)||'' days'')::INTERVAL - ''1 days''::INTERVAL;
RETURN endsin;
END;
' LANGUAGE 'plpgsql';
On Fri, 2003-02-07 at 14:57, Luis Magaña wrote:
Hi,
how can I get the first and last day of a week given the year and the
week number ???
example
week 3 of 2003 starts in Jan 12th and ends on Jan 18th.
how to know the last two values given the year and the number of week ?
thanks.
Best Regards.
--
Luis Magaña.
Gnovus Networks Software.
www.gnovus.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] start and end of the week
On Thu, Sep 26, 2002 at 18:56:46 +0800, John Sebastian N. Mayordomo [EMAIL PROTECTED] wrote: How do I get the start and end date of the present week? Is this possible? For example this week Start = Sept. 22 End = Sept. 28 The following advice will work on 7.3. For 7.2.2 and earlier it might not work during a week with a timezone change depending at what time of day you switch between DST and ST. To fix this you need to cast current_date to a timestamp without timezone, and I haven't been able to figure out how to do that and have to run off to a meeting now. For the first day of the current week use something like: area= select current_date - extract(dow from current_date) * area- '1 day'::interval; ?column? - 2002-09-22 00:00:00 (1 row) For the last day of the week use something like: area= select current_date + (6 - extract(dow from current_date)) * area- '1 day'::interval; ?column? - 2002-09-28 00:00:00 (1 row) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] start and end of the week
How about: select now() - date_part( 'DOW', now()) as starts_on, now() -date_part( 'DOW', now()) + 6 as ends_on; John Sebastian N. Mayordomo wrote: How do I get the start and end date of the present week? Is this possible? For example this week Start = Sept. 22 End = Sept. 28 Thank you very much. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] start and end of the week
On Thu, Sep 26, 2002 at 11:55:48 -0400, Jean-Luc Lachance [EMAIL PROTECTED] wrote: How about: select now() - date_part( 'DOW', now()) as starts_on, now() -date_part( 'DOW', now()) + 6 as ends_on; That won't work in 7.3. The following works in both 7.2 and 7.3: area= select current_date - extract(dow from current_date)::int as start_date, area- current_date - extract(dow from current_date)::int + 6 as end_date; start_date | end_date + 2002-09-22 | 2002-09-28 (1 row) Extract returns double precision and so needs a cast to int to work. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] start and end of the week
Does any one know what is the reason not to put this logic into date_trunc () function? It seems to work with pretty much *any* unit imaginable, *except* for 'week'... Dima Bruno Wolff III wrote: On Thu, Sep 26, 2002 at 11:55:48 -0400, Jean-Luc Lachance [EMAIL PROTECTED] wrote: How about: select now() - date_part( 'DOW', now()) as starts_on, now() -date_part( 'DOW', now()) + 6 as ends_on; That won't work in 7.3. The following works in both 7.2 and 7.3: area= select current_date - extract(dow from current_date)::int as start_date, area- current_date - extract(dow from current_date)::int + 6 as end_date; start_date | end_date + 2002-09-22 | 2002-09-28 (1 row) Extract returns double precision and so needs a cast to int to work. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
