pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing a variable as a view name in a select
6+ messages / 4 participants
[nested] [flat]
* Using a variable as a view name in a select
@ 2007-04-03 13:04 Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>
0 siblings, 3 replies; 6+ messages in thread
From: Wilkinson, Jim @ 2007-04-03 13:04 UTC (permalink / raw)
To: pgsql-sql
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 ?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using a variable as a view name in a select
@ 2007-04-03 13:16 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>
2 siblings, 0 replies; 6+ messages in thread
From: A. Kretschmer @ 2007-04-03 13:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using a variable as a view name in a select
@ 2007-04-03 13:21 John Summerfield <postgres@herakles.homelinux.org>
parent: Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>
2 siblings, 0 replies; 6+ messages in thread
From: John Summerfield @ 2007-04-03 13:21 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using a variable as a view name in a select
@ 2007-04-03 14:14 Hilary Forbes <hforbes@dmr.co.uk>
parent: Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>
2 siblings, 1 reply; 6+ messages in thread
From: Hilary Forbes @ 2007-04-03 14:14 UTC (permalink / raw)
To: Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>; pgsql-sql
<html>
<body>
Jim<br><br>
My initial reaction is what are you trying to achieve? Surely you
could have one underlying table with dates 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 type=cite class=cite cite=""><font size=2 color="#000080">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
…<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 ?</font></blockquote>
<x-sigsep><p></x-sigsep>
Hilary Forbes<br>
DMR Limited (UK registration 01134804) <br>
A DMR Information and Technology Group company
(<a href="http://www.dmr.co.uk/"; eudora="autourl"><font color="#0000FF">
<u>www.dmr.co.uk</a></u></font>) <br>
Direct tel 01689 889950 Fax 01689 860330 <br>
DMR is a UK registered trade mark of DMR Limited<br>
**********************************************************</body>
</html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using a variable as a view name in a select
@ 2007-04-03 16:45 Hilary Forbes <hforbes@dmr.co.uk>
parent: Hilary Forbes <hforbes@dmr.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Hilary Forbes @ 2007-04-03 16:45 UTC (permalink / raw)
To: Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>; +Cc: pgsql-sql
<html>
<body>
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 starting
in 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 BY date_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 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!<br><br>
Now I have NO idea on the efficiency of this as I rather suspect
all those date_trunc functions 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 type=cite class=cite cite=""><font size=2 color="#000080">Hi
Hilary, <br>
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<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"></font></div>
<font face="Tahoma" size=2><b>From:</b> Hilary Forbes
[<a href="mailto:hforbes@dmr.co.uk" eudora="autourl">
mailto:hforbes@dmr.co.uk</a>] <br>
<b>Sent:</b> April 3, 2007 10:14 AM<br>
<b>To:</b> Wilkinson, Jim; pgsql-sql@postgresql.org<br>
<b>Subject:</b> Re: [SQL] Using a variable as a view name in a
select<br>
</font><font face="Times New Roman, Times"> <br>
Jim<br><br>
My initial reaction is what are you trying to achieve? Surely you
could have one underlying table with dates 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>
<br>
</font><font face="Times New Roman, Times" size=2 color="#000080">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
…<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 registration 01134804) <br>
A DMR Information and Technology Group company (
<a href="http://www.dmr.co.uk/"; eudora="autourl">www.dmr.co.uk</a>) <br>
Direct tel 01689 889950 Fax 01689 860330 <br>
DMR is a UK registered trade mark of DMR Limited<br>
**********************************************************</font>
</blockquote>
<x-sigsep><p></x-sigsep>
Hilary Forbes<br>
DMR Limited (UK registration 01134804) <br>
A DMR Information and Technology Group company
(<a href="http://www.dmr.co.uk/"; eudora="autourl"><font color="#0000FF">
<u>www.dmr.co.uk</a></u></font>) <br>
Direct tel 01689 889950 Fax 01689 860330 <br>
DMR is a UK registered trade mark of DMR Limited<br>
**********************************************************</body>
</html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using a variable as a view name in a select
@ 2007-04-03 17:18 Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>
parent: Hilary Forbes <hforbes@dmr.co.uk>
0 siblings, 0 replies; 6+ messages in thread
From: Wilkinson, Jim @ 2007-04-03 17:18 UTC (permalink / raw)
To: Hilary Forbes <hforbes@dmr.co.uk>; +Cc: pgsql-sql
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
<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
<http://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
<http://www.dmr.co.uk/; )
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**********************************************************
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-04-03 17:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-03 13:04 Using a variable as a view name in a select Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>
2007-04-03 13:16 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-03 13:21 ` John Summerfield <postgres@herakles.homelinux.org>
2007-04-03 14:14 ` Hilary Forbes <hforbes@dmr.co.uk>
2007-04-03 16:45 ` Hilary Forbes <hforbes@dmr.co.uk>
2007-04-03 17:18 ` Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox