Thread: 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'# usr record; flipr'# results text; 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
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 jliang@ipinc.com 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'# usr record; > flipr'# results text; > 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 > >
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 egfor 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
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'# usr record; > flipr'# results text; > 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
Try:
create function foo(text) returns int4 as '
delcaretcount int4:=0;
beginfor 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
jliang@ipinc.com
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
>
>
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_matrixWHERE 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 20:23, 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