[SQL] Joint a table back on itself?
Hi all, I have a union query that generates a table with directional measurments (a=azimuth, i=depth) at various depths (md) down a hole. The results look like: hole_id | md | a| i|e |n |v ---++++--+--+-- GND-06-65 | 0 | 90 |-75 | 795187.927 | 9228405.685 | 3945.199 GND-06-65 | 19 | 90.37 | -74.42 | 795192.937315893 | 9228405.66852282 | 3926.87160812059 GND-06-65 | 28 | 91.18 | -74.49 | 795195.348994385 | 9228405.63593718 | 3918.20081588081 GND-06-65 | 37 | 91.04 | -74.53 | 795197.752173187 | 9228405.5893705 | 3909.52772202531 GND-06-65 | 46 | 92.38 | -74.56 | 795200.149282893 | 9228405.51783377 | 3900.85313364721 GND-06-65 | 55 | 92.86 | -74.55 | 795202.543576384 | 9228405.40826886 | 3892.17815120329 The depths ( md column) will always start with zero and the intervals will be variable. So how can I join this view back onto itself so each record is joined to the next record? Such as: md1 | a1 | i1 |e1| n1|v1| md2 | a2 | i2 |e2|n2| v2| +++--+--+--|+++--+--+-- 0 | 90 |-75 | 795187.927 | 9228405.685 | 3945.199 | 19 | 90.37 | -74.42 | 795192.937315893 | 9228405.66852282 | 3926.87160812059 19 | 90.37 | -74.42 | 795192.937315893 | 9228405.66852282 | 3926.87160812059 | 28 | 91.18 | -74.49 | 795195.348994385 | 9228405.63593718 | 3918.20081588081 28 | 91.18 | -74.49 | 795195.348994385 | 9228405.63593718 | 3918.20081588081 | 37 | 91.04 | -74.53 | 795197.752173187 | 9228405.5893705 | 3909.52772202531 37 | 91.04 | -74.53 | 795197.752173187 | 9228405.5893705 | 3909.52772202531 | 46 | 92.38 | -74.56 | 795200.149282893 | 9228405.51783377 | 3900.85313364721 46 | 92.38 | -74.56 | 795200.149282893 | 9228405.51783377 | 3900.85313364721 | 55 | 92.86 | -74.55 | 795202.543576384 | 9228405.40826886 | 3892.17815120329 My reason for wanting this is so I can joint this table with a between clause to another table with depth measurments recorded along this hole and perform a calculation. Thanks, Phillip J. Allen Consulting Geochemist [EMAIL PROTECTED] The union query is as follows: SELECT c.hole_id, 0 AS md, c.collar_azimuth AS a, c.collar_dip AS i, c.e_utm AS e, c.n_utm AS n, c.elv_utm AS v FROM dh_collar AS c WHERE (((c.hole_id)='GND-06-65')) UNION SELECT s.hole_id, s.depth_m AS md, s.azimuth AS a, s.vert_dip AS i, s.XE AS e, s.XN AS n, s.XR AS v FROM dh_survey AS s WHERE (((s.hole_id)='GND-06-65')) UNION SELECT s.hole_id, c.depth_m AS md, s.azimuth AS a, s.vert_dip AS i, s.XE AS e, s.XN AS n, s.XR AS v FROM dh_collar AS c RIGHT JOIN dh_survey AS s ON c.hole_id = s.hole_id WHERE (((s.depth_m)= ( SELECT Max(stmp.depth_m) AS MaxOfdepth_m FROM dh_survey AS stmp GROUP BY stmp.hole_id HAVING (((stmp.hole_id)='GND-06-65')) )) AND ((s.hole_id)='GND-06-65')); ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Update 3 columns w/ 1 function calc 3 values?
Hi all, I am moving some of my old MS Access functions to plpgsql. My function was made to return coordinates, X,Y,Z for a point along a curved line. So it is not the fastest of functions because it has to call and query data from 3 different tables. I used to just create 3 wrapper functions for my main function which would return the x, y, and z values seperately but that requires the main function to be called 3 seperate times. So here is my bottle neck. Of course I need to update +50,000 records. What should I do to speed this up. What is the best way to update 3 columns from one function call. In MSACCESS I once rig the function to cycle through the records with a curser but that was slow and would crap out due to the shear number. Thanks, Phil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Fw: [SQL] How to FindNearest
I guess I explained that very poorly. Sorry. It is actually much simplier. I have 3 tables Hole Hole_id X Y Z Down_hole_survey Hole_id Depth_meters Azimuth Vertical_inclination X Y Z Sample Hole_id Depth_meters X Y Z Hole_id is a primary key in the hole table while it is a foriegn key in down_hole_survey and sample tables. My function will take all the above info about the hole and down_hole_survey tables, and sample.hole_id and sample.depth_meters and will calculate the coordinates. I need to put these coordinates into sample.x , sample.y and sample.z . What I used to do was have one function that would return 3 values (x,y,z). Then create 3 more functions that would call call the main function and pull out the 3 values seperately to update the 3 seperate columns. It works fine but I have to call the main function 3 times which produces a slow performance. I hope that I explained it better this time. Phil Sent via BlackBerry from Cingular Wireless -Original Message- From: Bruno Wolff III <[EMAIL PROTECTED]> Date: Tue, 3 Oct 2006 12:11:25 To:Alexander Ilyin <[EMAIL PROTECTED]> Cc:[email protected] Subject: Re: [SQL] How to FindNearest On Tue, Oct 03, 2006 at 14:43:40 +0300, Alexander Ilyin <[EMAIL PROTECTED]> wrote: > > Thank you for your great idea. But how it can be used for positioning the > cursor in the already existed ResultSet? Using your idea I can found the > closest to targetvalue row but not its position in my ResultSet. You wouldn't be able to use it to position a cursor. But if you aren't retrieving a lot of records at once, this may still be a workable strategy for you. > Anyway thank you for your idea it is very useful by itself. Also I can solve > my problem using your idea and emulating the movement in my existed > ResultSet. Even better - no need to store the large RS between cursor > movements. Just each time I need to fetch the all visible rows. That sounds pretty reasonable. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Query to return schema/table/columname/columntype
Hi all, I am trying to modify the dabo (a python wxpython ide for database forms creation) code to allow the selection of tables in any schema. I need a query that will return records with schema, table, columname and columne type. For background I am selecting table & schema by the query: SELECT schemaname || '.' || tablename AS tablename FROM pg_tables ORDER BY tablename; Then I need to obtain the columns within that table/schema. The original query was: select c.oid,a.attname, t.typname from pg_class c inner join pg_attribute a on a.attrelid = c.oid inner join pg_type t on a.atttypid = t.oid where c.relname = 'thetablename and a.attnum > 0; Now my problem is how to create a new column to replace c.relname so I can query on "theschemaname.thetablename". Any suggestions will be welcomed. Thanks, Phil ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Query to return schema/table/columname/columntype
Hi all, I think I have fixed my own problem. At: http://developer.postgresql.org/~momjian/upgrade_tips_7.3 I found the answer which was: SELECT a.attrelid as oid, a.attname, t.typname FROM pg_attribute a inner join pg_type t on a.atttypid = t.oid WHERE a.attrelid = 'co.hole_test'::regclass AND a.attnum > 0 ORDER BY a.attnum; thanks anyway! Phil Hi all, I am trying to modify the dabo (a python wxpython ide for database forms creation) code to allow the selection of tables in any schema. I need a query that will return records with schema, table, columname and columne type. For background I am selecting table & schema by the query: SELECT schemaname || '.' || tablename AS tablename FROM pg_tables ORDER BY tablename; Then I need to obtain the columns within that table/schema. The original query was: select c.oid,a.attname, t.typname from pg_class c inner join pg_attribute a on a.attrelid = c.oid inner join pg_type t on a.atttypid = t.oid where c.relname = 'thetablename and a.attnum > 0; Now my problem is how to create a new column to replace c.relname so I can query on "theschemaname.thetablename". Any suggestions will be welcomed. Thanks, Phil ---(end of broadcast)--- TIP 1: 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
[SQL] Permissions Query?
Hi all, still working on dabo a bit. Thanks for all the earlier suggestions, they are working well. I now want to know if the user/role has permision to the schema.table. How do I query to pgsql to know what permissions a particular user has on the table? I was thinking something along the lines of a query that maybe produces results like this. schematablep_select p_delete p_all p_update .. public.tbl1 10 0 0 public.tbl2 11 1 1 public.tbl00 0 1 I am intersted to see if I can filter what tables/schemas are avaliable to the user to avoid error messages due to permissions. thanks, Phil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Update Field with function/data from other tables?
Hi all, I want to update the values of a column with the result of a function that requires information from another table. Specifically it needs a minium and maximum value from another table. How do I perform the update query? Below is my attempt but it doesn't work. The "bhlineid" is my primary key and foreign key in the 2 tables UPDATE geo_obs.bhline SET bhline_geom = CalcBH3dLineMC (minmax.bhlineid, minmax.srid_original, minmax.from_m, minmax.to_m, 'geo_obs.bhline_surv', 'bhlineid', 'dist_m', 'azim', 'v_inclin', 'bhline_surv_geom', 5.0) FROM (SELECT i.bhlineid AS bhlineid, min(i.from_m) AS from_m, max(i.to_m) AS to_m, max(c.srid_original) AS srid_original FROM geo_obs.bhline_int AS i, geo_obs.bhline AS c WHERE c.bhlineid=i.bhlineid GROUP BY i.bhlineid) AS minmax WHERE geo_obs.bhline.bhlineid = minmax.bhlineid ; Thanks for any help. Phillip Allen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Converting from MS Access field aliases
Joel, To avoid ms access from managing the query, use a pass-through query. Then access will send the raw sql statment to psql and psql will return just the results to access. It will speed things up a bit too for large datasets. Phillip allen Sent via BlackBerry by AT&T -Original Message- From: Joel Richard <[EMAIL PROTECTED]> Date: Thu, 12 Jul 2007 13:36:05 To:Tom Lane <[EMAIL PROTECTED]> Cc:sql pgsql Subject: Re: [SQL] Converting from MS Access field aliases On Jul 12, 2007, at 1:23 PM, Tom Lane wrote: > chester c young <[EMAIL PROTECTED]> writes: >>> SELECT field1 / 2 AS foo, >>> field2 * 2 AS bar, >>> foo + bar AS total >>> WHERE foo < 12; > >> First, I think it would be great if this worked - like the alias >> to an >> update table added in 8.2 - saves a lot of typing and makes queries >> much more readable. > > This is not an "extension", it is *directly* contrary to both the > letter > and spirit of the SQL standard. I can hardly believe that M$ did that > ... oh, actually, I can entirely believe it. The OP has a serious > problem of vendor lockin now, and that's exactly what M$ wants. > > regards, tom lane Hear hear! What's really screwy is what I found when I hooked access into my PostgreSQL database using pgsqlODBC (I know, it's an abomination) and I logged the statements that PostgreSQL was processing. In MS Access this query: SELECT foo AS bar, bar * 2 AS gleep FROM table; became this in PostgreSQL's logs SELECT foo FROM table; Vewwy Intewesting! I think Microsoft and ODBC might be making extra work for themselves (obviously they are since they are allowing these aliases) Or maybe not. Maybe I can glean something from this. Who knows. --Joel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
