Обсуждение: LOCK command inside a TRANSACTION

<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 />
**********************************************************