pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: What's wrong with this function
6+ messages / 4 participants
[nested] [flat]

* What's wrong with this function
@ 2001-02-10 18:04 Najm Hashmi <najm@mondo-live.com>
  2001-02-09 18:26 ` Re: What's wrong with this function Jie Liang <jliang@ipinc.com>
  2001-02-09 21:17 ` Re: What's wrong with this function plpgsql@polar-digital.com
  0 siblings, 2 replies; 6+ messages in thread

From: Najm Hashmi @ 2001-02-10 18:04 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: What's wrong with this function
  2001-02-10 18:04 What's wrong with this function Najm Hashmi <najm@mondo-live.com>
@ 2001-02-09 18:26 ` Jie Liang <jliang@ipinc.com>
  2001-02-10 20:23   ` Re: What's wrong with this function Najm Hashmi <najm@mondo-live.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Jie Liang @ 2001-02-09 18:26 UTC (permalink / raw)
  To: Najm Hashmi <najm@mondo-live.com>; +Cc: pgsql-sql


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
> 
> 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: What's wrong with this function
  2001-02-10 18:04 What's wrong with this function Najm Hashmi <najm@mondo-live.com>
  2001-02-09 18:26 ` Re: What's wrong with this function Jie Liang <jliang@ipinc.com>
@ 2001-02-10 20:23   ` Najm Hashmi <najm@mondo-live.com>
  2001-02-09 21:03     ` Re: What's wrong with this function mark proctor <m.proctor@bigfoot.com>
  2001-02-09 21:43     ` Re: What's wrong with this function Jie Liang <jliang@ipinc.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Najm Hashmi @ 2001-02-10 20:23 UTC (permalink / raw)
  To: Jie Liang <jliang@ipinc.com>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: What's wrong with this function
  2001-02-10 18:04 What's wrong with this function Najm Hashmi <najm@mondo-live.com>
  2001-02-09 18:26 ` Re: What's wrong with this function Jie Liang <jliang@ipinc.com>
  2001-02-10 20:23   ` Re: What's wrong with this function Najm Hashmi <najm@mondo-live.com>
@ 2001-02-09 21:03     ` mark proctor <m.proctor@bigfoot.com>
  1 sibling, 0 replies; 6+ messages in thread

From: mark proctor @ 2001-02-09 21:03 UTC (permalink / raw)
  To: Najm Hashmi <najm@mondo-live.com>; Jie Liang <jliang@ipinc.com>; +Cc: pgsql-sql

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 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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: What's wrong with this function
  2001-02-10 18:04 What's wrong with this function Najm Hashmi <najm@mondo-live.com>
  2001-02-09 18:26 ` Re: What's wrong with this function Jie Liang <jliang@ipinc.com>
  2001-02-10 20:23   ` Re: What's wrong with this function Najm Hashmi <najm@mondo-live.com>
@ 2001-02-09 21:43     ` Jie Liang <jliang@ipinc.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Jie Liang @ 2001-02-09 21:43 UTC (permalink / raw)
  To: Najm Hashmi <najm@mondo-live.com>; +Cc: pgsql-sql

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

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
> 
> 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: What's wrong with this function
  2001-02-10 18:04 What's wrong with this function Najm Hashmi <najm@mondo-live.com>
@ 2001-02-09 21:17 ` plpgsql@polar-digital.com
  1 sibling, 0 replies; 6+ messages in thread

From: plpgsql@polar-digital.com @ 2001-02-09 21:17 UTC (permalink / raw)
  To: Najm Hashmi <najm@mondo-live.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-02-10 20:23 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-10 18:04 What's wrong with this function Najm Hashmi <najm@mondo-live.com>
2001-02-09 18:26 ` Jie Liang <jliang@ipinc.com>
2001-02-10 20:23   ` Najm Hashmi <najm@mondo-live.com>
2001-02-09 21:03     ` mark proctor <m.proctor@bigfoot.com>
2001-02-09 21:43     ` Jie Liang <jliang@ipinc.com>
2001-02-09 21:17 ` plpgsql@polar-digital.com

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox