[SQL] save data from views
first of all, thanx for anyone who answered my previous question... i've understood what was wrong... now, i've got another question, i know it could sound stupid, but i have not such a great practice with postgres. i'm asking you: is it that a way to save values from a view? i'm using postgres to calculate pollutant emissions by cars, and i make some queries and create views. i would like to save the results from the views but i can't find a way to export them. once again thanx in advance for your help, massimo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] save data from views
On Mon, 23 Dec 2002, [iso-8859-1] [EMAIL PROTECTED] wrote: > > > > first of all, thanx for anyone who answered my previous question... i've understood >what was wrong... > now, i've got another question, i know it could sound stupid, but i have not such a >great practice with postgres. i'm asking you: is it that a way to save values from a >view? > i'm using postgres to calculate pollutant emissions by cars, and i make some queries >and create views. i would like to save the results from the views but i can't find a >way to export them. > once again thanx in advance for your help, massimo You could write a program in your favourite language that selects and prints the rows of your views. Also you could # create table tempviewdata as select * from ; Why would you want to save the values of your views?? > > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] save data from views
On Mon, 23 Dec 2002, [iso-8859-1] [EMAIL PROTECTED] wrote: > > > > first of all, thanx for anyone who answered my previous question... i've understood >what was wrong... > now, i've got another question, i know it could sound stupid, but i have not such a >great practice with postgres. i'm asking you: is it that a way to save values from a >view? > i'm using postgres to calculate pollutant emissions by cars, and i make some queries >and create views. i would like to save the results from the views but i can't find a >way to export them. > once again thanx in advance for your help, massimo You could write a program in your favourite language that selects and prints the rows of your views. Also you could # create table tempviewdata as select * from ; Why would you want to save the values of your views?? thanx for your answer. i'm developing a database that outputs pollutant emissions that has to be imported by the GRASS _ GIS program. in that way i can produce vector maps of the city i'm studying with pollutant emissions as attributes. to do this, i had to put the COPERT emissions model in the database, and create views to display all the results. but i also need to save this results, if i want to open them in other programs!!! massimo ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] DB2 to Postgresql conversion help.
All, Any suggestions on a Postgresql equivalent to the following DB2 sql query: SELECT * FROM (SELECT ID,NAME,CITY,STATE,PROGRAM,CUST_NAME,CUST_ID,ROWNUMBER() OVER (ORDER BY ID ASC) AS RN FROM MERCHANT_BROWSER_VIEW WHERE CUST_ID = 'A51B8CA2' ) AS RESULT WHERE RN BETWEEN 1 AND 20 TIA, Happy Holidays, jp __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] DB2 to Postgresql conversion help.
John Pauley wrote: All, Any suggestions on a Postgresql equivalent to the following DB2 sql query: SELECT * FROM (SELECT ID,NAME,CITY,STATE,PROGRAM,CUST_NAME,CUST_ID,ROWNUMBER() OVER (ORDER BY ID ASC) AS RN FROM MERCHANT_BROWSER_VIEW WHERE CUST_ID = 'A51B8CA2' ) AS RESULT WHERE RN BETWEEN 1 AND 20 I'm not familiar with DB2, but I'd guess something like: SELECT ID,NAME,CITY,STATE,PROGRAM,CUST_NAME,CUST_ID FROM MERCHANT_BROWSER_VIEW WHERE CUST_ID = 'A51B8CA2' ORDER BY ID LIMIT 20; HTH, Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] corresponding primary key for max(column)?
is there a better way?
for reasons not defined by me, i cannot constrain the data in the
locations table with unique(ref,ts)
that said Microsoft mappoint craps out if there are duplicate values in
the ref column.
so i need to create a view which returns locations.* for the row which
contains the max(ts) group by ref.
-jason pyeron
assetdb=# \d locations
Table "locations"
Column | Type | Modifiers
+--+--
id | integer | not null default
nextval('"locations_id_seq"'::text)
ref| integer | not null
ts | timestamp with time zone |
lat| numeric(12,10) | not null
lon| numeric(13,10) | not null
disp | character varying(63)|
Primary key: locations_pkey
assetdb=# select * from locations ;
id | ref | ts | lat | lon |
disp
+-+---+---++--
1 | 1 | 2002-12-22 05:47:26.863774-05 | 39.337307 | -76.6245916667 | 0.00MPH
038.6 degrees
2 | 1 | 2002-12-22 05:52:57.211416-05 | 39.337307 | -76.6245916667 | 0.00MPH
038.6 degrees
3 | 1 | 2002-12-22 05:54:18.125055-05 | 39.337307 | -76.6245916667 | 0.00MPH
038.6 degrees
4 | 2 | 2002-12-22 06:04:48.348906-05 | 40.337307 | -76.2459166670 | 0.00MPH
038.6 degrees
5 | 2 | 2002-12-22 06:04:48.348906-05 | 99.99 | -99.99 | Foo Data
Point 0.00MPH 038.6 degrees
6 | 1 | 2001-01-01 00:00:00-05| 38.00 | -76.90 | dfdsfsd
(6 rows)
assetdb=# SELECT
assetdb-# l0.id, l0.ref, l0.ts, l0.lat, l0.lon, l0.disp
assetdb-#
assetdb-# FROM
assetdb-# locations l0
assetdb-#
assetdb-# WHERE
assetdb-# l0.id =
assetdb-# ANY (
assetdb(# SELECT
assetdb(#min(l1.id) AS max
assetdb(#
assetdb(# FROM
assetdb(#(
assetdb(# SELECT
assetdb(# l2.id, l2.ref, l2.ts, l2.lat, l2.lon, l2.disp
assetdb(#
assetdb(# FROM
assetdb(# locations l2
assetdb(#
assetdb(# WHERE
assetdb(# l2.ts =
assetdb(# (
assetdb(# SELECT
assetdb(#max(l3.ts) AS max
assetdb(# FROM
assetdb(#locations l3
assetdb(# WHERE
assetdb(#l3.ref = l2.ref
assetdb(# )
assetdb(#) as l1
assetdb(#
assetdb(# GROUP BY
assetdb(#l1.ref
assetdb(# )
assetdb-# ;
id | ref | ts | lat | lon |
disp
+-+---+---++---
3 | 1 | 2002-12-22 05:54:18.125055-05 | 39.337307 | -76.6245916667 | 0.00MPH
038.6 degrees
4 | 2 | 2002-12-22 06:04:48.348906-05 | 40.337307 | -76.2459166670 | 0.00MPH
038.6 degrees
(2 rows)
Seq Scan on locations l0 (cost=0.00..22535105.55 rows=500 width=98)
SubPlan
-> Materialize (cost=22535.08..22535.08 rows=1 width=8)
-> Aggregate (cost=22535.06..22535.08 rows=1 width=8)
-> Group (cost=22535.06..22535.07 rows=5 width=8)
-> Sort (cost=22535.06..22535.06 rows=5 width=8)
-> Seq Scan on locations l2 (cost=0.00..22535.00 rows=5
width=8)
SubPlan
-> Aggregate (cost=22.51..22.51 rows=1 width=8)
-> Seq Scan on locations l3
(cost=0.00..22.50 rows=5 width=8)
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
- -
- Jason Pyeron http://www.pyerotechnics.com -
- Owner & Lead Pyerotechnics Development, Inc. -
- +1 410 808 6646 (c) 500 West University Parkway #1S -
- +1 410 467 2266 (f) Baltimore, Maryland 21210-3253 -
- -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
This message is for the designated recipient only and may contain
privileged, proprietary, or otherwise private information. If you
have received it in error, purge the message from your system and
notify the sender immediately. Any other use of the email by you
is prohibited.
---(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
