[SQL] How do i return a dataset from a stored procedure
I can't figure out how to return a group of rows from a function written in plpgsql (or plsql for that matter). The only way i have seen it done is with a setof table return value in sql. But since the query was a single select anyway i don't see the point of this. Is there a way of creating a temporary table in a procedure and using it as the output? thanks ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Can this be done with sql?
In my database i have values recorded in one minute intervals. I would like a query that can get me results for other time intervals. For example - return maximum value in each 3 minute interval. Any ideas how i can do this with sql? I tried writing a procedure in plsql but i am told it does not support tuples as output. I can get the all the one minute intervals and process them to get me three minute intervals in my application but i would rather not do the expensive call for the one minute intervals in the first place due to the large number of data. any ideas? thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Can this be done with sql?
""Rajesh Kumar Mallah."" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Hi , > > you can use GROUP BY , at the expense of adding one more column of SERIAL d= > ata type, > > say, > > select * from t_a limit 10; > access_log=3D# SELECT * from t_a limit 15; > > sno | value > -+--- >1 | 4533 >2 | 2740 >3 | 9970 > >4 | 6445 >5 | 2220 >6 | 2301 > >7 | 6847 >8 | 5739 >9 | 5286 > > 10 | 5556 > 11 | 9309 > 12 | 9552 > > 13 | 8589 > 14 | 5935 > 15 | 2382 > (15 rows) > > if you want avg for every third item you can use: > > access_log=3D# SELECT avg(value) from t_a group by (1+(sno-1)/3) limit 5;= > =20=20 > yes, thank you, that may help but unfortunately there are are few more problems to face. 1. I will need to select groups from anywhere in the table so i cannot assume that 1 will be the start number. They will be contigous however so i can use another query top get the start number but is it possible to do it with just one select? 2. I need to display not just aggregates but the first and last value in the group for two of the fields. I mean by this that i need opening_value(field1) and closing_value(field2). 3. If this needs to be done via stored procedure how do i get it to return a result set. I've tried setof record but it doesn't work. thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] manipulating the database in plsql
Is there any way to work with tables etc, in plsql? Can i get a get a database handle to the local database? If not, is there any chance for this to be implemented in the near future? thanks ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Updating a table column with ref integrity
I need to to change the type of a table column. The problem is that this table has a ref integrity constarint set up with another table. Does anyone know how I can do this easily without breaking any constraints. thanks ---(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
[SQL] help with troublesome query
I have three tables customers which 1-many with requests which is 1-1 with applications. all customers have at least one request but not all requests have an application. I want a query to return all the customers and their application data if they have any ( or else null). So this is like a left join of customers with applications but I do not want multiple rows of customers UNLESS they have an application. thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] converting interval to timestamp
The difference of two dates/timestamps always gives an interval. is there a way to convert this interval into number such as number of milliseconds or number of days? Also does anyone know what field type an interval would map to in jdbc? thanks ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] does postgresql execute unions in parallel?
If I have several selects joined with unions does postgresql execute the concurrently or not? thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] row and column transposition
Anyone know some sql that display a row as a set of rows with column name vs column value? ---(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
[SQL] indexing timestamp fields
Is it a good idea to index timestamp fields? what about date fields in general? thanks ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] indexing timestamp fields
Christopher Browne wrote: teknokrat <[EMAIL PROTECTED]> writes: Is it a good idea to index timestamp fields? what about date fields in general? If you need to order by a timestamp, then it can be worthwhile. If that timestamp can be null, and is rarely populated, then you might get a _big_ benefit from creating a partial index as with: create index by_some_date on my_table(some_date) where some_date is not null; I have a lot of queries of the " where timestamp < some date " type and was wondering if an index would improve performance. None of the timestamps are null and they are always populated ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] how do i get differences between rows
I am after an sql query that can give the differnces between consecutive timestamp values in the resultset. anyone know how to do this? thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] how do i get differences between rows
Bruno Wolff III wrote: On Thu, Dec 18, 2003 at 12:21:35 +, teknokrat <[EMAIL PROTECTED]> wrote: I am after an sql query that can give the differnces between consecutive timestamp values in the resultset. anyone know how to do this? I think you can do something like the following (untested) on most systems. select a.stamp - (select max(stamp) from tablename where tablename.stamp < a.stamp) from tablename; For postgres this (also untested) might be faster if there is an index on stamp. select a.stamp - (select stamp from tablename where tablename.stamp < a.stamp order by stamp desc limit 1) from tablename; The above relies on timestamps being unique. The difference for the smallest timestamp will be null. ---(end of broadcast)--- TIP 8: explain analyze is your friend this may give me difference between current row and oldest row but what I want is row(i).timestamp - row(i-1).timestamp. Also I would like to do it for timestamp values whose hours are within normal working hours i.e 9-5. ---(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
[SQL] grouping by date
How can I group by date given a timestamp column? thanks ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
