Thread: LOCK command inside a TRANSACTION
I need Postgresql somehow does this for me:
- if an user query a select on a table, the rows of the table in the result of this select can not be updated or deleted by another user until this one update, delete or discard the changes on those rows.
I've found something about the LOCK command inside a TRANSACTION but I didn't see how I could do that yet.
Does anybody have any ideas?
Compels Informática
Santa Rita do Sapucaí - MG
Brazil
www.compels.net
__________________________________________________
Fale com seus amigos de graça com o novo Yahoo! Messenger
http://br.messenger.yahoo.com/
<div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">I have created a view, called april_may. I need to select this view by combineingto fields in the database to create the view name etc …</span></font><p class="MsoNormal"><font color="navy" face="Arial"size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">Create view as select * from table_X;</span></font><p class="MsoNormal"><font color="navy"face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">I need to do something like this … </span></font><p class="MsoNormal"><font color="navy"face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">Select * from (select table.start_month||_||table.end_month);</span></font><p class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">==================</span></font><p class="MsoNormal"><font color="navy" face="Arial"size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">Start_month = april</span></font><p class="MsoNormal"><font color="navy" face="Arial"size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">End_month = May</span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><spanstyle="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">What I what to pass to the select is the combination of the 2 fields as the view name.</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">Any ideas ?</span></font></div>
am Tue, dem 03.04.2007, um 9:04:00 -0400 mailte Wilkinson, Jim folgendes: > I have created a view, called april_may. I need to select this view by > combineing to fields in the database to create the view name etc ? Please, no answer to an other mail and change the subject to a new subject. Your mail sorted in the wrong thread. > Select * from (select table.start_month||_||table.end_month); > > ================== > > Start_month = april > > End_month = May > > > > What I what to pass to the select is the combination of the 2 fields as the > view name. Perhaps with a function that receive start and end and returns the result as a table (set-returning function) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Wilkinson, Jim wrote: > I have created a view, called april_may. I need to select this view by > combineing to fields in the database to create the view name etc ... Jim Learn to use "compose" or "write" and not "reply" when you want to ask a fresh question. My email rolled this into the "LOCK" thread making it incredibly difficult to find. > > > > Create view as select * from table_X; > > > > I need to do something like this ... > > > > Select * from (select table.start_month||_||table.end_month); > > ================== > > Start_month = april > > End_month = May > > > > What I what to pass to the select is the combination of the 2 fields as > the view name. > > > > Any ideas ? The simple ways I can think of are external to postgresql, and depend on your host environment. For example, on Linux (or OS X) I might do something like this: psql -c "select * from (select table.$(date +%B -d 'last month')_table.$(date +%B))" Note, the above para is really a single line, there is a space between "last" and "month." on DOS it might be a little trickier, but probably VB Script can do it. In Java, it is somewhat similar but different. If this doesn't answer, give better info and maybe someone else can help you properly.
Am Dienstag, 3. April 2007 14:48 schrieb Carlos Santos: > - if an user query a select on a table, the rows of the table in the result > of this select can not be updated or deleted by another user until this one > update, delete or discard the changes on those rows. Sounds like SELECT FOR UPDATE. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Jim<br /><br /> My initial reaction is what are you trying to achieve? Surely you could have one underlying table withdates in it and<br /><br /> SELECT * from mytable WHERE date1>='2007/04/01' AND date2<='2007/05/01';<br /><br />but otherwise, like John, I would use an external scripting language to create the table name.<br /><br /> Hilary<br /><br/> At 14:04 03/04/2007, Wilkinson, Jim wrote:<br /><br /><blockquote cite="" class="cite" type="cite"><font color="#000080"size="2">I have created a view, called april_may. I need to select this view by combineing to fields inthe database to create the view name etc <br /> <br /> Create view as select * from table_X;<br /> <br /> I need todo something like this <br /> <br /> Select * from (select table.start_month||_||table.end_month);<br /> ==================<br/> Start_month = april<br /> End_month = May<br /> <br /> What I what to pass to the select is thecombination of the 2 fields as the view name.<br /> <br /> Any ideas ?</font></blockquote><p> Hilary Forbes<br /> DMRLimited (UK registration 01134804) <br /> A DMR Information and Technology Group company (<a eudora="autourl" href="http://www.dmr.co.uk/"><fontcolor="#0000FF"> <u>www.dmr.co.uk</u></font></a>) <br /> Direct tel 01689 889950 Fax 01689860330 <br /> DMR is a UK registered trade mark of DMR Limited<br /> **********************************************************
Jim<br /><br /> So let's suppose you have a "master" table of incidents<br /><br /> incident_no (serial)<br />
incident_date(timestamp)<br /> other fields<br /><br /> My understanding is that you now want to eg count the incidents
startingin a given month and going forwards for 12 months, grouping the results by month. Have I understood the
problem?<br/><br /> If so here goes:<br /><br /> Set up a table hftest<br /><br /> incident serial<br /> incdate
timestamp<br/><br /> SELECT * from hftest;<br /> incident | incdate<br /> ----------+---------------------<br />
1000 | 2006-05-03 00:00:00<br /> 1001 | 2006-04-03 00:00:00<br /> 1002 | 2006-04-01 00:00:00<br />
1003| 2006-12-08 00:00:00<br /> 1004 | 2007-02-28 00:00:00<br /> 1005 | 2007-08-03 00:00:00<br /><br />
Now:<br/> SELECT max(to_char(incdate,'Mon')) ,count(incident) from hftest WHERE date_trunc('month',incdate)
>='2006/04/01'AND date_trunc('month',incdate)<=date_trunc('month',date '2006/04/01' + interval '12 months') GROUP
BYdate_trunc('month',incdate) ORDER BY date_trunc('month',incdate);<br /> max | count<br /> -----+-------<br /> Apr
| 2<br /> May | 1<br /> Dec | 1<br /> Feb | 1<br /> <br /> which is almost what you want. To get
themissing months with zeroes, I think you probably need a table of months and to use a left outer join but you may
havefound a better way by now!<br /><br /> Now I have NO idea on the efficiency of this as I rather suspect all those
date_truncfunctions may have an adverse effect!<br /><br /> Best regards<br /> Hilary<br /> <br /><br /><br /><br
/><br/><br /><br /> At 16:44 03/04/2007, you wrote:<br /><br /><blockquote cite="" class="cite" type="cite"><font
color="#000080"size="2">Hi Hilary, <br /> I am trying to produce reports where the user can select a different fiscal
yearstarting month. From this I would select the correct table view to produce the reports in the correct month order
bycolumn<br /> <br /> Select * from table_view;<br /> <br /> Incident April May June
July Aug
.<br /> ===============================================<br /> Falls 1
0 1 0 0<br /> .<br /> .<br /> .<br /> .<br /> <br /> Can you think of another way to do this
?<br/> <br /> <br /><hr /><div align="center"></div></font> <font face="Tahoma" size="2"><b>From:</b> Hilary Forbes
[<aeudora="autourl" href="mailto:hforbes@dmr.co.uk"> mailto:hforbes@dmr.co.uk</a>] <br /><b>Sent:</b> April 3, 2007
10:14AM<br /><b>To:</b> Wilkinson, Jim; pgsql-sql@postgresql.org<br /><b>Subject:</b> Re: [SQL] Using a variable as a
viewname in a select<br /></font><font face="Times New Roman, Times"> <br /> Jim<br /><br /> My initial reaction is
whatare you trying to achieve? Surely you could have one underlying table with dates in it and<br /><br /> SELECT *
frommytable WHERE date1>='2007/04/01' AND date2<='2007/05/01';<br /><br /> but otherwise, like John, I would use
anexternal scripting language to create the table name.<br /><br /> Hilary<br /><br /> At 14:04 03/04/2007, Wilkinson,
Jimwrote:<br /><br /><br /></font><font color="#000080" face="Times New Roman, Times" size="2">I have created a view,
calledapril_may. I need to select this view by combineing to fields in the database to create the view name etc
<br
/> <br /> Create view as select * from table_X;<br /> <br /> I need to do something like this
<br /> <br /> Select
*from (select table.start_month||_||table.end_month);<br /> ==================<br /> Start_month = april<br />
End_month= May<br /> <br /> What I what to pass to the select is the combination of the 2 fields as the view name.<br
/> <br /> Any ideas ?<br /></font><br /><font face="Times New Roman, Times">Hilary Forbes<br /> DMR Limited (UK
registration01134804) <br /> A DMR Information and Technology Group company ( <a eudora="autourl"
href="http://www.dmr.co.uk/">www.dmr.co.uk</a>)<br /> Direct tel 01689 889950 Fax 01689 860330 <br /> DMR is a UK
registeredtrade mark of DMR Limited<br />
**********************************************************</font></blockquote><p>Hilary Forbes<br /> DMR Limited (UK
registration01134804) <br /> A DMR Information and Technology Group company (<a eudora="autourl"
href="http://www.dmr.co.uk/"><fontcolor="#0000FF"> <u>www.dmr.co.uk</u></font></a>) <br /> Direct tel 01689 889950 Fax
01689860330 <br /> DMR is a UK registered trade mark of DMR Limited<br />
**********************************************************
Almost, in the table there are multiple different incidents.
Incident April May June July Aug ….
===============================================
Falls 1 0 1 0 0
Roof Area 0 1 0 0 2
Complaints.. 1 2 3 2 2
Etc …
What I need to do is to be able to change the column heading to have a different start and finish month
Etc …
Incident Feb Mar Apr May June ….
==============================================
Falls 1 0 1 0 0
Roof Area 0 1 0 0 2
Complaints.. 1 2 3 2 2
The only way I can think of is to create 12 differents views with the months in order and then concatenating the start_month and end_month fields in the database to create the view name. Then do a select with the created view name.
Select * from May_June;
.
.
.
From: Hilary Forbes [mailto:hforbes@dmr.co.uk]
Sent: April 3, 2007 12:45 PM
To: Wilkinson, Jim
Cc: pgsql-sql@postgresql.org
Subject: RE: [SQL] Using a variable as a view name in a select
Jim
So let's suppose you have a "master" table of incidents
incident_no (serial)
incident_date (timestamp)
other fields
My understanding is that you now want to eg count the incidents starting in a given month and going forwards for 12 months, grouping the results by month. Have I understood the problem?
If so here goes:
Set up a table hftest
incident serial
incdate timestamp
SELECT * from hftest;
incident | incdate
----------+---------------------
1000 | 2006-05-03 00:00:00
1001 | 2006-04-03 00:00:00
1002 | 2006-04-01 00:00:00
1003 | 2006-12-08 00:00:00
1004 | 2007-02-28 00:00:00
1005 | 2007-08-03 00:00:00
Now:
SELECT max(to_char(incdate,'Mon')) ,count(incident) from hftest WHERE date_trunc('month',incdate) >='2006/04/01' AND date_trunc('month',incdate)<=date_trunc('month',date '2006/04/01' + interval '12 months') GROUP BY date_trunc('month',incdate) ORDER BY date_trunc('month',incdate);
max | count
-----+-------
Apr | 2
May | 1
Dec | 1
Feb | 1
which is almost what you want. To get the missing months with zeroes, I think you probably need a table of months and to use a left outer join but you may have found a better way by now!
Now I have NO idea on the efficiency of this as I rather suspect all those date_trunc functions may have an adverse effect!
Best regards
Hilary
At 16:44 03/04/2007, you wrote:
Hi Hilary,
I am trying to produce reports where the user can select a different fiscal year starting month. From this I would select the correct table view to produce the reports in the correct month order by column
Select * from table_view;
Incident April May June July Aug ….
===============================================
Falls 1 0 1 0 0
.
.
.
.
Can you think of another way to do this ?
From: Hilary Forbes [ mailto:hforbes@dmr.co.uk]
Sent: April 3, 2007 10:14 AM
To: Wilkinson, Jim; pgsql-sql@postgresql.org
Subject: Re: [SQL] Using a variable as a view name in a select
Jim
My initial reaction is what are you trying to achieve? Surely you could have one underlying table with dates in it and
SELECT * from mytable WHERE date1>='2007/04/01' AND date2<='2007/05/01';
but otherwise, like John, I would use an external scripting language to create the table name.
Hilary
At 14:04 03/04/2007, Wilkinson, Jim wrote:
I have created a view, called april_may. I need to select this view by combineing to fields in the database to create the view name etc …
Create view as select * from table_X;
I need to do something like this …
Select * from (select table.start_month||_||table.end_month);
==================
Start_month = april
End_month = May
What I what to pass to the select is the combination of the 2 fields as the view name.
Any ideas ?
Hilary Forbes
DMR Limited (UK registration 01134804)
A DMR Information and Technology Group company ( www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**********************************************************
Hilary Forbes
DMR Limited (UK registration 01134804)
A DMR Information and Technology Group company ( www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**********************************************************