[SQL] how to write procedures
Hi, I am using postgre sql server on linux server but for my database I am using storedprocedures which i need to create , but there are no commands to create procedures it says it does not support is there any way to work with stored procedures in postgre sql server. thanks, srikanth. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
Dear all, I have a table that records User Login Sessions with two timestamp fields. Basically Start of Session and End of a Session (start_ts and end_ts). Each row in the table identifies a session which a customer has used. Data from the table (session): - customer_id | log_session_id | start_ts | end_ts -+-++ 1006100716 | 209571229340224 | 15/12/2008 16:53:52.665327 | 15/12/2008 16:58:52.665327 1006100789 | 112061228488202 | 05/12/2008 20:13:32.773065 | 09/12/2008 22:59:02.770218 1006100888 | 214221233045949 | 27/01/2009 14:15:16.289626 | 27/01/2009 14:58:59.989182 100608 | 205221236839534 | 12/03/2009 12:02:15.947509 | 12/03/2009 12:07:15.947509 1006100825 | 112331229068049 | 12/12/2008 13:17:37.229249 | 12/12/2008 13:56:58.394577 The requirement is as follows, I have to find out how many User Sessions that were present in any given "1 HOUR TIME PERIOD". A single User Session can span across many days. Example: start_ts | end_ts 05/12/2008 20:13:32.773065 | 09/12/2008 22:59:02.770218 - Let me explain a scenario, I have to find out the number of sessions during the interval '07/01/2009 11:00:00' to '07/01/2009 11:59:59'. If I have to find the number of sessions present at any instant time say '07/01/2009 11:25:25', i can easily find out using the following Query, select count(log_session_id) from session where '07/01/2009 11:25:25' between start_ts and end_ts ; But, I have to find the number of sessions present during the "HOUR INTERVAL" '07/01/2009 11:00:00' to '07/01/2009 11:59:59'. I tried using wildcards in timestamp '07/01/2009 11:%:%" but in vain. I tries googling / searching archives without any success either. I feel this is a general requirement and this topic should have already been discussed. Could someone help me solve this please ? Any lead would do, like some special postgres-function or any other means. Many Thanks, ../rssrik Add more friends to your messenger and enjoy! Go to http://messenger.yahoo.com/invite/
Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
That did the job. Thanks. Am new to SQL, does not even know that there exists an Operator called OVERLAPS. Thanks Richard ../rssrik --- On Tue, 17/3/09, Richard Huxton wrote: From: Richard Huxton Subject: Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps To: "Srikanth" Cc: pgsql-sql@postgresql.org Date: Tuesday, 17 March, 2009, 8:36 PM Srikanth wrote: > Dear all, > > I have a table that records User Login Sessions with two timestamp fields.. > Basically Start of Session and End of a Session (start_ts and end_ts). Each > row in the table identifies a session which a customer has used. > > Data from the table (session): > - > customer_id | log_session_id | start_ts | end_ts > -+-++ > 1006100716 | 209571229340224 | 15/12/2008 16:53:52.665327 | 15/12/2008 >16:58:52.665327 > 1006100789 | 112061228488202 | 05/12/2008 20:13:32.773065 | 09/12/2008 >22:59:02.770218 > 1006100888 | 214221233045949 | 27/01/2009 14:15:16.289626 | 27/01/2009 >14:58:59.989182 > 100608 | 205221236839534 | 12/03/2009 12:02:15.947509 | 12/03/2009 >12:07:15.947509 > 1006100825 | 112331229068049 | 12/12/2008 13:17:37.229249 | 12/12/2008 >13:56:58.394577 > > > The requirement is as follows, > > I have to find out how many User Sessions that were present in any given "1 > HOUR TIME PERIOD". A single User Session can span across many days. SELECT * FROM session WHERE (start_ts,end_ts) OVERLAPS (, ); > I tried using wildcards in timestamp '07/01/2009 11:%:%" but in vain. I tries googling / searching archives without any success either. I'd have thought OVERLAPS would be mentioned in the date/time handling sections of the manual. -- Richard Huxton Archonet Ltd Add more friends to your messenger and enjoy! Go to http://messenger.yahoo.com/invite/
[SQL] PIVOT of data
Hi, I have a table like this: location| numbrochures | marketing ---+--+--- 101 Asheville, NC |4 | NEWS 101 Asheville, NC |1 | TV 101 Asheville, NC |3 | RADIO 101 Asheville, NC |2 | OTHER 101 Asheville, NC |3 | null 101 Asheville, NC |1 | TV 102 'Charlotte', 'NC' |1 | SIGN 104 'Colfax', 'NC'|5 | SIGN 109 'Moyock', 'NC'|1 | BROCHURE (9 rows) I want the headings to be like: location | NEWS | TV | RADIO | OTHER | How to get this done using sql for postgresql backend? TIA, Sri. __ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices. http://auctions.yahoo.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Memory exhaustion
marketingbyoffice is a view. I start the postmaster, then issue the following query in psql console. It goes to sleep forever:-) Why? The log is as follows: --log - 010404.13:49:14.612 [1352] StartTransactionCommand 010404.13:49:14.612 [1352] query: SELECT * INTO TEMP TABLE marketingbylocation FROM marketingbyoffice; 010404.13:49:14.624 [1352] ProcessQuery 010404.13:52:28.691 [1352] FATAL 1: Memory exhausted in AllocSetAlloc() 010404.13:52:32.568 [1352] AbortCurrentTransaction __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Counting rows in a table
MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii I have a query as follows: SELECT date(c.timestart) as ondate, c.userid, u.username, count(c.callinfoid) as numCalls, sum(b.broquant) as numBroc, sum(b.broquant)/count(c.callinfoid) as BrochuresaCall FROM callinfo c, user_ u, brochurerequest b WHERE c.userid = u.userid AND b.callinfoid = c.callinfoid GROUP BY c.userid,u.username,ondate; But, I want numCalls to count rows where b.callinfoid is not in c.callinfoid. How do i do it ? I am on version 7.0.2. Below are the table create statements that I have. - CREATE TABLE Patient ( patientId INT8, personId INT8 , constraint Patient_key primary key (patientId) ); CREATE TABLE User_ ( userId INT8, userName TEXT constraint uname_uniq unique, userPassword TEXT, userRole INTEGER, userMode TEXT, personId INT8, constraint User_key primary key (userId) ); CREATE TABLE CallInfo ( callInfoId INT8, timeStart TIMESTAMP, timeStop TIMESTAMP, marketing TEXT, userId INT8 constraint CI_U_FK references User_, patientId INT8, constraint CallInfo_key primary key (callinfoId) ); CREATE TABLE BrochureRequest ( broQuant INTEGER, method TEXT, comment TEXT, officeId INT8, callInfoId INT8 constraint BR_CI_FK references CallInfo ); __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Restricting the number of decimal digits
I have a query that calculates the average of difference between timestamps. I want the output to be upto 1 decimal point. How do I do it? __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] How to store a linked list in a RDBMS
I have a linked list representing a tree. How do I store it in the database? Does PGSQL give us any special procedures to deal with such structures? __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Cant execute the query
When i am executing this query, i am facing the select s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.actno,a.actname,p.descr as svcdescr from vwsubsmin s inner join packages p on s.svcno=p.pkgno inner join account a on a.actno=s.actno inner join ssgdom d on a.domno=d.domno inner join (select subsno from getexpiringsubs($1,cast($2 as integer),cast($3 as double precision),$4) as (subsno int,expirydt timestamp without time zone,balcpt double precision)) as e on s.subsno=e.subsno where s.status <=15 and d.domno=$5 order by d.domname,s.expirydt,a.actname Error is : ERROR: there is no parameter $1 LINE 5: inner join (select subsno from getexpiringsubs($1,cast($2 as... ^ ** Error ** ERROR: there is no parameter $1 SQL state: 42P02 Character: 295 Please suggest on this -- View this message in context: http://old.nabble.com/Cant-execute-the-query-tp29055726p29055726.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
