[SQL] Index Problem
I want to query this... --> SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12; this query doesn't refer the index that made by this query. --> CREATE INDEX idx_bbs ON bbs (ref, step); but, i change the query that "ref desc" to "ref asc". then query refer the index, and i can see a result very fast. :-( so, i want to view an result that one column ordered by ascending, and oterh column ordered by descending using index. what do i do? how make an index? == == ¿ì¸® ÀÎÅͳÝ, Daum Æò»ý ¾²´Â ¹«·á E-mail ÁÖ¼Ò ÇѸÞÀÏ³Ý Áö±¸ÃÌ ÇÑ±Û °Ë»ö¼ºñ½º Daum FIREBALL http://www.daum.net
[SQL] pgsql and cursor
Hi all, I am not sure If it is allowed to use cursor inside pgsql functions. If it is possible, please someone could send exact synatx how it is used. If it is not allowed is there a way arround it? I need to do some calculations and then return this value as text. Thanks in advance for all your help. Najm
[SQL] What's wrong with this function
Hi all, Here is a plpgsql function: flipr'#create function test_cur() returns text as' flipr'# declare flipr'# mycur cursor for select title from songs where song_id=10; flipr'# usrrecord; flipr'# resultstext; flipr'# begin flipr'# open mycur; flipr'# fetch next from mycur into usr; flipr'# close mycur; flipr'# results:= usr.title; flipr'# flipr'# end; flipr'# ' language 'plpgsql'; CREATE flipr=# select test_cur() as Title; NOTICE: plpgsql: ERROR during compile of test_cur near line 2 ERROR: parse error at or near "cursor" What I am doing wrong? Thanks in advance for your help. Regards, Najm
Re: [SQL] pgsql and cursor
I just know you can use implict cursor inside the plpgsql e.g declare rec record; begin FOR rec IN select_clause LOOP statements END LOOP; end; 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.ipinc.com On Sat, 10 Feb 2001, Najm Hashmi wrote: > Hi all, I am not sure If it is allowed to use cursor inside pgsql functions. > If it is possible, please someone could send exact synatx how it is used. If > it is not allowed is there a way arround it? I need to do some calculations > and then return this value as text. > Thanks in advance for all your help. > Najm >
Re: [SQL] What's wrong with this function
I just know you can use implict cursor inside the plpgsql e.g declare rec record; begin FOR rec IN select_clause LOOP statements END LOOP; end; 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.ipinc.com On Sat, 10 Feb 2001, Najm Hashmi wrote: > Hi all, > Here is a plpgsql function: > flipr'#create function test_cur() returns text as' > flipr'# declare > flipr'# mycur cursor for select title from songs where song_id=10; > flipr'# usrrecord; > flipr'# resultstext; > flipr'# begin > flipr'# open mycur; > flipr'# fetch next from mycur into usr; > flipr'# close mycur; > flipr'# results:= usr.title; > flipr'# > flipr'# end; > flipr'# ' language 'plpgsql'; > CREATE > flipr=# select test_cur() as Title; > NOTICE: plpgsql: ERROR during compile of test_cur near line 2 > ERROR: parse error at or near "cursor" > > What I am doing wrong? > Thanks in advance for your help. > Regards, Najm > >
[SQL] PL/PGSQL Cook Book
I've just spent the last day or two trying to get to grips with plpgsql and can't believe how abysmal the documetentation and examples are. I've been trawling through the mailist lists and I notice there was talk back in 1999 abouta PLPGSQL Cook Book - did anything come of this? If no one is maintaining something like this and people think its a good idea I think we should have another crack at it. I'd be happy to maintain something like this and put it up on the web, although I'm only a newbie and would rely upon user contribution. Here are some possible sections to help get people thinking. Even if you don't know the answer send me the questions and I'll add them to the list. How can I create Tree structures? Are recursive functions supported? Whats are the advanteds disadvantes of the different scipting langues PL/PGSQL, PL/Perl, PL/Tcl? How do variable scopes work in PL/PGSQL? Can I output variables from a function to the command line for debugging purposes? How to debug PL/PGSQL? Various examples for each of the statements Anyway lets discuss this, a lot could be done just from piecing together relavent tips from this mailing list. ie there are some good posts on tree structures, which if I'm willing to piece together if people think this project is worth while. Regards Mark
[SQL] Recusrive Functions in 7.0.3
Are recursive functions allowed in in 7.0.3 as I seem to be unable to get them to work in plpgsql, it just freezes and I have to do ctrl-alt-c. ie calling the same function name from within some form of loop, possible to 4 or 5 levels? Also is it possible to output any debugging info, all I really want to be able to do is print out a variable to the screen. Final quesion for pure speed on functions does PL/PGSQL have special compilation options or would I get better performace for PL/C? Regards Mark
Re: [SQL] What's wrong with this function
Jie Liang wrote: > I just know you can use implict cursor inside the plpgsql > e.g > declare result text; tcount int4; > > rec record; > begin > FOR rec IN select_clause LOOP > statements > END LOOP; > end; > Thank you Jie for your help. I am bit confused about how it works. I want for each row , obtained by select statment, get certain values and then do some calculations and out put that resulst eg for rec IN select title, dcount from songs where artist='xyz' tcount:= tcount+rec.dcount; END LOOP; return tcount; would this work ? Thanks again for your help. Regards, Najm
[SQL] String Concatnation
Hi, How can I concatnate two varialbles, seperated by a |, that are type text together? v, v1 text; some work then res:= v ||''|''|| v1; this syntex does not work in plpgsql?? Any ideas how to do it ??? Thanks. Najm
Re: [SQL] What's wrong with this function
the select query returns the first row to rec. You can then access its values with: rec.field_name at END LOOP it jumps back to FOR checks to see if there any more rows and if so moves to the next row and repeats the loop. It also looks like your missing a LOOP keyword at the end of the FOR line. Here is an example that works. CREATE FUNCTION get_children (integer) RETURNS integer AS ' DECLARE pnode_parent ALIAS FOR $1; rec RECORD; BEGIN FOR rec IN SELECT * FROM tree_adjacency_matrix WHERE node_parent = pnode_parent LOOP INSERT INTO test (node1, node2) VALUES(stm.node_child, .rec.node_parent); END LOOP; RETURN 0; END; 'LANGUAGE 'plpgsql' Mark On Saturday 10 February 2001 18:04, Najm Hashmi wrote: > Hi all, > Here is a plpgsql function: > flipr'#create function test_cur() returns text as' > flipr'# declare > flipr'# mycur cursor for select title from songs where song_id=10; > flipr'# usrrecord; > flipr'# resultstext; > flipr'# begin > flipr'# open mycur; > flipr'# fetch next from mycur into usr; > flipr'# close mycur; > flipr'# results:= usr.title; > flipr'# > flipr'# end; > flipr'# ' language 'plpgsql'; > CREATE > flipr=# select test_cur() as Title; > NOTICE: plpgsql: ERROR during compile of test_cur near line 2 > ERROR: parse error at or near "cursor" > > What I am doing wrong? > Thanks in advance for your help. > Regards, Najm
Re: [SQL] String Concatnation
On Sat, 10 Feb 2001, Najm Hashmi wrote: > How can I concatnate two varialbles, seperated by a |, that are type text > together? > v, v1 text; > some work > then > res:= v ||''|''|| v1; What error is it giving? Do you need to be using two single quotes in the statement? -- Brett http://www.chapelperilous.net/~bmccoy/ --- There is a certain impertinence in allowing oneself to be burned for an opinion. -- Anatole France
Re: [SQL] String Concatnation
I was able to do a function that took two arguments and did that under 7.1beta3 with no trouble. What message are you getting? On Sat, 10 Feb 2001, Najm Hashmi wrote: > Hi, > How can I concatnate two varialbles, seperated by a |, that are type text > together? > v, v1 text; > some work > then > res:= v ||''|''|| v1; > this syntex does not work in plpgsql?? > Any ideas how to do it ??? > Thanks. > Najm >
Re: [SQL] pgsql and cursor
Najm Hashmi <[EMAIL PROTECTED]> writes: > Hi all, I am not sure If it is allowed to use cursor inside pgsql functions. > If it is possible, please someone could send exact synatx how it is used. If > it is not allowed is there a way arround it? I need to do some calculations > and then return this value as text. Cursors are not supported in PL/pgSQL. (You can, however, use FOR to loop over a query). For a patch to add cursor support to PL/pgSQL, see http://www.airs.com/ian/pgsql-cursor.html Ian
Re: [SQL] What's wrong with this function
Try:
create function foo(text) returns int4 as '
delcare
tcount int4:=0;
begin
for rec IN select title, dcount from songs where artist=$1 LOOP
tcount:= tcount+rec.dcount;
END LOOP;
return tcount;
end;
' language 'plpgsql';
call it by:
db> select foo('Najm Hashmi');
it will return how many songs of 'Najm Hashmi' in your database.
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 Sat, 10 Feb 2001, Najm Hashmi wrote:
> Jie Liang wrote:
>
> > I just know you can use implict cursor inside the plpgsql
> > e.g
> > declare
>
> result text;
> tcount int4;
>
> >
> > rec record;
> > begin
> > FOR rec IN select_clause LOOP
> > statements
> > END LOOP;
> > end;
> >
>
> Thank you Jie for your help. I am bit confused about how it works. I want for
> each row , obtained by select statment, get certain values and then do some
> calculations and out put that resulst eg
> for rec IN select title, dcount from songs where artist='xyz'
> tcount:= tcount+rec.dcount;
> END LOOP;
> return tcount;
> would this work ?
> Thanks again for your help.
> Regards, Najm
>
>
Re: [SQL] String Concatnation
Hi, You can use every sql function and operator in plpgsql, so v||''|''||v2 is OK. however, you cannot do: declare v,v2 text; you should do: v text; v2 text; also you initialize like: v text:=; 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 Sat, 10 Feb 2001, Najm Hashmi wrote: > Hi, > How can I concatnate two varialbles, seperated by a |, that are type text > together? > v, v1 text; > some work > then > res:= v ||''|''|| v1; > this syntex does not work in plpgsql?? > Any ideas how to do it ??? > Thanks. > Najm >
[SQL] Re: PL/PGSQL Cook Book
On Friday 09 February 2001 12:23, [EMAIL PROTECTED] wrote: > I've just spent the last day or two trying to get to grips with plpgsql and > can't believe how abysmal the documetentation and examples are. I've been > trawling through the mailist lists and I notice there was talk back in 1999 > abouta PLPGSQL Cook Book - did anything come of this? a good body of open source plpgsql code is in the openacs distribution at openacs.org. i agree that plpgsql is poorly documented, the only way i found to figure out its capabilities were to scour the mailing lists, the openacs code, and the plpgsql implementation source. any attempt to better this documentation would IMO, be a worthwhile effort. hth kapil
Re: [SQL] Re: PL/PGSQL Cook Book
[ Charset ISO-8859-1 unsupported, converting... ] > On Friday 09 February 2001 12:23, [EMAIL PROTECTED] wrote: > > I've just spent the last day or two trying to get to grips with plpgsql and > > can't believe how abysmal the documetentation and examples are. I've been > > trawling through the mailist lists and I notice there was talk back in 1999 > > abouta PLPGSQL Cook Book - did anything come of this? > > a good body of open source plpgsql code is in the openacs distribution at > openacs.org. > > i agree that plpgsql is poorly documented, the only way i found to figure out > its capabilities were to scour the mailing lists, the openacs code, and the > plpgsql implementation source. any attempt to better this documentation would > IMO, be a worthwhile effort. I have added PL/PgSQL to the open 7.1 docs list. Let's see if someone takes a stab at it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[SQL] how to download postgresql 7.1 beta
thanks
Re: [SQL] Recusrive Functions in 7.0.3
<[EMAIL PROTECTED]> writes: > Are recursive functions allowed in in 7.0.3 Sure. play=> create function myfactorial(int) returns int as ' play'> begin play'> if $1 > 1 then play'> return $1 * myfactorial($1 - 1); play'> end if; play'> return $1; play'> end;' language 'plpgsql'; CREATE play=> select myfactorial(1); myfactorial - 1 (1 row) play=> select myfactorial(10); myfactorial - 3628800 (1 row) play=> I get a stack overflow crash at about myfactorial(7500), but that seems like a sufficient level of recursion depth for normal purposes ... > as I seem to be unable to > get them to work in plpgsql, Are you sure you aren't asking for infinite recursion, eg by invoking the same function with the same argument? regards, tom lane
