pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

How to execute cursor in PostgreSQL?
2+ messages / 2 participants
[nested] [flat]

* How to execute cursor in PostgreSQL?
@ 2015-02-27 03:59  Vinaya Torne <v.b.torne@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Vinaya Torne @ 2015-02-27 03:59 UTC (permalink / raw)
  To: pgsql-admin

Hi Admin

Below  is my program for cursor, but I am not getting how to execute the
cursor on a database.

Please suggest on this

emp=# CREATE or replace function cursor_demo() returns integer as $$
declare
emp_rec employee%rowtype;
emp cursor for select * from employee;
comm numeric(6,2);
begin
loop
fetch emp into emp_rec;
if emp_rec.deptno = 5 then
comm:=emp_rec.salary*0.2;
else if emp_rec.deptno=8 then
comm:= emp_rec.salary*0.5;
else if emp_rec.deptno = 10 then
comm:=emp_rec.salary*0.3;
end if;
end if;
end if;
raise notice 'emp_rec.ename||emp_rec.deptno||emp_rec.salary||comm.';
exit when not found;
end loop;
close emp;
end;
$$ language'plpgsql';

Thanks&Regards,
Vinaya Torne
 +91-9403150377

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

* Re: How to execute cursor in PostgreSQL?
@ 2015-02-27 04:40  David G Johnston <david.g.johnston@gmail.com>
  parent: Vinaya Torne <v.b.torne@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: David G Johnston @ 2015-02-27 04:40 UTC (permalink / raw)
  To: pgsql-admin

Vinaya Torne wrote
> Hi Admin
> 
> Below  is my program for cursor, but I am not getting how to execute the
> cursor on a database.
> 
> Please suggest on this
> 
> emp=# CREATE or replace function cursor_demo() returns integer as $$
> declare
> emp_rec employee%rowtype;
> emp cursor for select * from employee;
> comm numeric(6,2);
> begin
> loop
> fetch emp into emp_rec;
> if emp_rec.deptno = 5 then
> comm:=emp_rec.salary*0.2;
> else if emp_rec.deptno=8 then
> comm:= emp_rec.salary*0.5;
> else if emp_rec.deptno = 10 then
> comm:=emp_rec.salary*0.3;
> end if;
> end if;
> end if;
> raise notice 'emp_rec.ename||emp_rec.deptno||emp_rec.salary||comm.';
> exit when not found;
> end loop;
> close emp;
> end;
> $$ language'plpgsql';

I don't understand the question/problem.

I'm not seeing how this is an -admin concern; its better directed to
-general.

I'll assume the above is meant for learning because that is really bad form
if you intend it to be a solution to a problem.

pl/pgsql allows you to loop without making use of actual cursors.

I've never really made use of cursors...

You execute a function by doing: "SELECT cursor_demo();" > which will toss
an error because you have no "RETURN int" statement...there may be other
syntax issues with what you have written as well.

You should read much of the documentation; but as relates to this question:

http://www.postgresql.org/docs/9.4/interactive/plpgsql.html
and specifically
http://www.postgresql.org/docs/9.4/interactive/plpgsql-cursors.html

if you do end up convinced that a pl/pgsql cursor is a proper solution to
your, unstated, problem.

David J.




--
View this message in context: http://postgresql.nabble.com/How-to-execute-cursor-in-PostgreSQL-tp5839670p5839672.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



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

end of thread, other threads:[~2015-02-27 04:40 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-02-27 03:59 How to execute cursor in PostgreSQL? Vinaya Torne <v.b.torne@gmail.com>
2015-02-27 04:40 ` David G Johnston <david.g.johnston@gmail.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