Re: [SQL] C/C++ interface
>> Original Message << On 2/16/01, 8:24:09 AM, Volker Paul <[EMAIL PROTECTED]> wrote regarding Re: [SQL] C/C++ interface: > Tom Lane wrote: > > > > Volker Paul <[EMAIL PROTECTED]> writes: > > > Is someone maintaining the C++ interface and its documentation? > > > > Not really. Feel free to step up and lend a hand ... > I would like to complete the documentation at these points, > and maybe add some small example C++ programs. > In which form should I write the documentation and where > should I send it? > (I have no possibility at the moment to test SGML documents, > i.e. convert them to HTML.) > Regards, > Volker Paul I believe there are guidelines in the developer's section of the postgresql.org website - towards the end of the developer's docs. - Richard Huxton
[SQL] PL/PgSQL FOR syntax
Hi, What's the correct syntax to access rows in a FOR loop? I'm writing a PL/PgSQL doc and seem to be making a mistake somewhere. I am referring to this: [<>] FOR record | row IN select_clause LOOP statements END LOOP; How do I access the rows within the for loop? row.field? Thanks, -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
[SQL] Re: Trigger question
Actually, a default of now() would be perfectly providing you created a rule to trap insert's and updates and remove the setting for that time/date from being inserted so as to have the db use the dfault which I think is far easier that making a silly trigger for something so simple. Dan Dave Wedwick wrote: > One person suggested setting now() as default. > > That would work sometimes, but I have a situation where the user program is > updating the default field with 0. now() never gets into the field. > > So, regardless of what the user enters, I want now() to be updated, and I > guess it would be via a trigger... > > Thanks! > > Dave Wedwick wrote: > > > Hi! > > > > I have a table with an int4 field called inserttime. Regardless of what > > the user enters in this field, I want a trigger to put now() into it. > > > > What's the syntax for the trigger? > > > > Thanks!
[SQL] Data Types
Hello. I have a table in which I'm trying to store the length of a sound file. I decided to use the TIME data type. Was this correct? One of the operations I want to do is sum() all of my files lengths to get the total amount in terms of time, of sound that I have. I notice that sum() doesn't take a TIME argument, so I cast it to an interval as such: SELECT SUM( length::interval ) FROM songs; However this gives me output that I don't know how to read: '7 02:34:27' Does that read as 7 Days, 2 Hours, 34 Minutes and 27 seconds? TIA -- Dave
Re: [SQL] problem with casting
In data Wed, 14 Feb 2001 21:10:30 + (UTC), postgresql scrive: >I have a table that has a 'date' column. In psql I tried to do a > >select proofdate::text from openjobs where jobno = '10625'; > >responce: can not cast type 'date' to 'text' > >Did i miss-read this section. I thought I could cast the date to text. has anyone >seen >this or am I missreading the docs? > select to_char(proofdate, 'dd/mm/') from openjobs where jobno = '10625';
Re: [SQL] Data Types
David, > However this gives me output that I don't know how to read: '7 02:34:27' > Does that read as 7 Days, 2 Hours, 34 Minutes and 27 seconds? Yes. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
[SQL] Suggestions on finetuning this search?
I would welcome any suggestions for fine-tuning this search to run faster. Here is the SQL. Basically what we're allowing people to do is to specify words to search our article index. THE TABLES: knowledge = the knowledge base of articles kb_categories = the category that each article is assigned to kbwords = an index of every word in the knowledge base (kbid, wordid) wordindex = an index of every word in the knowledge base (wordid, word) AMPLIFICATION: kbwords = a list of all the words that appear in a specific article (might return a list of 5 articles where the word 'monk' appears) wordindex = a unique list of all the words that appear in all the articles (would only return a single entry for the word 'monk') THE SQL: select * from knowledge k, kb_categories c , kbwords w0 , kbwords w1 WHERE k.catid=c.catid AND ((k.kbid=w0.kbid and w0.wordid=(SELECT wordid from wordindex where word='BUDDIST')) AND (k.kbid=w1.kbid and w1.wordid=(SELECT wordid from wordindex where word='MONK'))) ORDER BY k.regionid , k.ctryid , k.catid , k.title ; WHAT "EXPLAIN" RETURNS: Sort (cost=2796577.40..2796577.40 rows=2878549 width=332) InitPlan -> Index Scan using wordindex_word on wordindex (cost=0.00..247.71 rows=376 width=4) -> Index Scan using wordindex_word on wordindex (cost=0.00..247.71 rows=376 width=4) -> Merge Join (cost=21187.45..21993.59 rows=2878549 width=332) -> Merge Join (cost=11060.50..11140.94 rows=52582 width=324) -> Sort (cost=933.56..933.56 rows=960 width=316) -> Hash Join (cost=1.43..885.97 rows=960 width=316) -> Seq Scan on knowledge k (cost=0.00..559.25 rows=2825 width=284) -> Hash (cost=1.34..1.34 rows=34 width=32) -> Seq Scan on kb_categories c (cost=0.00..1.34 rows=34 width=32) -> Sort (cost=10126.95..10126.95 rows=5474 width=8) -> Seq Scan on kbwords w0 (cost=0.00..9787.02 rows=5474 width=8) -> Sort (cost=10126.95..10126.95 rows=5474 width=8) -> Seq Scan on kbwords w1 (cost=0.00..9787.02 rows=5474 width=8) Please e-mail suggestions to [EMAIL PROTECTED] Thanks! To see the code in action, visit http://www.strategicnetwork.org/index.asp?loc=kb
[SQL] Datetime Query
I need to create a query which will select a request_no between Data1 and Date2 so... SELECT request_no FROM request where status_code ='C' and (completed_date between 01/01/2000 and 01/01/2001); The problem I have run into is that the completed_date field is a datetime format (not by my own design) and I am having some problems extracting just the request_no's between those dates. I have tried a few extract month,day,year clauses with no success. If anyone has an idea I would appreciate it! Thanks in advance. Mark
[SQL] Help Retrieving Latest Record
Hi, I am not sure if this went through the first time so here it is again. Ok what I am trying to do is select out of the database the latest record meeting a certain criteria. Example: Number | Fruit | Date 15 Apples July 20, 1999 20 Oranges June 7, 2000 13 Pears Jan 31, 2000 17 Apples April 10, 1999 Now what I need to do is select the oranges out because the date is the latest one, something like: select * from basket where max(date); This would yield me: 20 Oranges June 7, 2000 I know this doesn't work but I need something like it. or something like select * from basket where max(date) and fruit='Apples'; This would yield me: 15 Apples July 20, 1999 Thank you in advance, -- Steve Meynell Candata Systems
[SQL] Help retrieving lastest record
Ok what I am trying to do is select out of the database the latest record meeting a certain criteria. Example: Number | Fruit | Date 15 Apples July 20, 1999 20 Oranges June 7, 2000 13 Pears Jan 31, 2000 17 Apples April 10, 1999 Now what I need to do is select the oranges out because the date is the latest one, something like: select * from basket where max(date); This would yield me: 20 Oranges June 7, 2000 I know this doesn't work but I need something like it. or something like select * from basket where max(date) and fruit='Apples'; This would yield me: 15 Apples July 20, 1999 Thank you in advance, -- Steve Meynell Candata Systems
Re: [SQL] Help retrieving lastest record
Get an SQL book. select * from basket where date=(select max(date) where fruit='Apples) and fruit='Apples'; On Thu, 15 Feb 2001, Steve Meynell wrote: > Ok what I am trying to do is select out of the database the latest > record meeting a certain criteria. > > Example: > > Number |Fruit | Date > 15Apples July 20, 1999 > 20OrangesJune 7, 2000 > 13 PearsJan 31, 2000 > 17 Apples April 10, 1999 > Now what I need to do is select the oranges out because the date is the > latest one, something like: > > select * from basket where max(date); > This would yield me: > 20OrangesJune 7, 2000 > > I know this doesn't work but I need something like it. > or something like > > select * from basket where max(date) and fruit='Apples'; > This would yield me: > 15Apples July 20, 1999 > > Thank you in advance, > > > -- > Steve Meynell > Candata Systems > > >
Re: [SQL] Help Retrieving Latest Record
It would be something like: select * from basket where Date=(SELECT max(Date) from basket); At 09:41 AM 2/16/2001 -0500, Steve Meynell wrote: >Hi, I am not sure if this went through the first time so here it is again. > > >Ok what I am trying to do is select out of the database the latest record >meeting a certain criteria. > >Example: > >Number |Fruit | Date >15Apples July 20, 1999 >20OrangesJune 7, 2000 >13 PearsJan 31, 2000 >17 Apples April 10, 1999 >Now what I need to do is select the oranges out because the date is the >latest one, something like: > >select * from basket where max(date); >This would yield me: >20OrangesJune 7, 2000 > >I know this doesn't work but I need something like it. >or something like > >select * from basket where max(date) and fruit='Apples'; >This would yield me: >15Apples July 20, 1999 > >Thank you in advance, > >-- >Steve Meynell >Candata Systems
Re: [SQL] Help Retrieving Latest Record
On Fri, Feb 16, 2001 at 09:41:09AM -0500, Steve Meynell wrote: > > I know this doesn't work but I need something like it. > or something like > > select * from basket where max(date) and fruit='Apples'; > This would yield me: > 15Apples July 20, 1999 Maybe you mean min? This is the earliest date you had in the set. Assuming the Date column is of datetime/timestamp type: test=# \d fruits Table "fruits" Attribute | Type | Modifier ---+---+-- fruit_id | integer | name | character varying(50) | date | timestamp | test=# select * from fruits; fruit_id | name| date --+---+ 1 | Orange| 2001-02-16 13:26:52-07 3 | Pineapple | 1999-10-04 00:00:00-06 2 | Apple | 2000-12-05 00:00:00-07 4 | Apple | 2000-07-01 00:00:00-06 (4 rows) test=# SELECT * FROM fruits WHERE name='Apple' ORDER BY date DESC LIMIT 1; fruit_id | name | date --+---+ 2 | Apple | 2000-12-05 00:00:00-07 (1 row) Look at the documentation for the ORDER BY and LIMIT clauses on the PG docs. -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] Help Retrieving Latest Record
SELECT * FROM basket ORDER BY date desc LIMIT 1; and SELECT * FROM basket WHERE fruit = 'apple' ORDER BY date desc LIMIT 1; Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Fri, 16 Feb 2001, Steve Meynell wrote: > Hi, I am not sure if this went through the first time so here it is > again. > > > Ok what I am trying to do is select out of the database the latest > record meeting a certain criteria. > > Example: > > Number |Fruit | Date > 15Apples July 20, 1999 > 20OrangesJune 7, 2000 > 13 PearsJan 31, 2000 > 17 Apples April 10, 1999 > Now what I need to do is select the oranges out because the date is the > latest one, something like: > > select * from basket where max(date); > This would yield me: > 20OrangesJune 7, 2000 > > I know this doesn't work but I need something like it. > or something like > > select * from basket where max(date) and fruit='Apples'; > This would yield me: > 15Apples July 20, 1999 > > Thank you in advance, > > > -- > Steve Meynell > Candata Systems > > >
Re: [SQL] Help retrieving lastest record
How 'bout these: fgdata=# select * from fruit order by dt desc limit 1; number | fruit | dt +-+ 20 | Oranges | 2000-06-07 00:00:00-05 (1 row) fgdata=# select * from fruit where fruit='Apples' order by dt desc limit 1; number | fruit | dt ++ 15 | Apples | 1999-07-20 00:00:00-05 (1 row) Cheers, Andy Perrin -- Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin [EMAIL PROTECTED] - [EMAIL PROTECTED] On Thu, 15 Feb 2001, Steve Meynell wrote: > Ok what I am trying to do is select out of the database the latest > record meeting a certain criteria. > > Example: > > Number |Fruit | Date > 15Apples July 20, 1999 > 20OrangesJune 7, 2000 > 13 PearsJan 31, 2000 > 17 Apples April 10, 1999 > Now what I need to do is select the oranges out because the date is the > latest one, something like: > > select * from basket where max(date); > This would yield me: > 20OrangesJune 7, 2000 > > I know this doesn't work but I need something like it. > or something like > > select * from basket where max(date) and fruit='Apples'; > This would yield me: > 15Apples July 20, 1999 > > Thank you in advance, > > > -- > Steve Meynell > Candata Systems > > >
Re: [SQL] Help Retrieving Latest Record
Steve Meynell <[EMAIL PROTECTED]> writes: > select * from basket where max(date) and fruit='Apples'; > I know this doesn't work but I need something like it. Perhaps select * from basket where fruit='Apples' order by date desc limit 1; The limit clause is not standard SQL, but it's a pretty easy answer if portability doesn't bother you... regards, tom lane
Re: [SQL] Datetime Query
Try:
SELECT request_no FROM request where status_code ='C' and
(completed_date::date
between '01/01/2000'::date and '01/01/2001'::date)
actually date('01/01/2000') does same thing as '01/01/2000'::date
Jie LIANG
St. Bernard Software
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.stbernard.com
www.ipinc.com
On Thu, 15 Feb 2001, Mark Byerley wrote:
> I need to create a query which will select a request_no between Data1 and
> Date2 so...
>
> SELECT request_no FROM request where status_code ='C' and (completed_date
> between 01/01/2000 and 01/01/2001);
>
> The problem I have run into is that the completed_date field is a datetime
> format (not by my own design) and I am having some problems extracting just
> the request_no's between those dates.
> I have tried a few extract month,day,year clauses with no success. If
> anyone has an idea I would appreciate it!
> Thanks in advance.
> Mark
>
Re: [SQL] Help Retrieving Latest Record
Subquery will do: select * from basket where Date in (select max(Date) from basket); Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 16 Feb 2001, Steve Meynell wrote: > Hi, I am not sure if this went through the first time so here it is > again. > > > Ok what I am trying to do is select out of the database the latest > record meeting a certain criteria. > > Example: > > Number |Fruit | Date > 15Apples July 20, 1999 > 20OrangesJune 7, 2000 > 13 PearsJan 31, 2000 > 17 Apples April 10, 1999 > Now what I need to do is select the oranges out because the date is the > latest one, something like: > > select * from basket where max(date); > This would yield me: > 20OrangesJune 7, 2000 > > I know this doesn't work but I need something like it. > or something like > > select * from basket where max(date) and fruit='Apples'; > This would yield me: > 15Apples July 20, 1999 > > Thank you in advance, > > > -- > Steve Meynell > Candata Systems > > >
Re: [SQL] PL/PgSQL FOR syntax
Yes, e.g. declare r record; begin for r in select statement loop r.fieldname can fetch the result from the select statment row by row Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 16 Feb 2001, Roberto Mello wrote: > Hi, > > What's the correct syntax to access rows in a FOR loop? I'm writing > a PL/PgSQL doc and seem to be making a mistake somewhere. > I am referring to this: > > [<>] > FOR record | row IN select_clause LOOP > statements > END LOOP; > > How do I access the rows within the for loop? row.field? > > Thanks, > > -Roberto > -- > Computer Science Utah State University > Space Dynamics Laboratory Web Developer > USU Free Software & GNU/Linux Clubhttp://fslc.usu.edu > My home page - http://www.brasileiro.net/roberto >
