From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: How to execute cursor in PostgreSQL?
Date: Thu, 26 Feb 2015 21:40:28 -0700 (MST)
Message-ID: <1425012028153-5839672.post@n5.nabble.com> (raw)
In-Reply-To: <CAF3Q6eC_5hGMyXn8+JrUCiS+8CY37nGtK-c3Va01KRddWyZAzg@mail.gmail.com>
References: <CAF3Q6eC_5hGMyXn8+JrUCiS+8CY37nGtK-c3Va01KRddWyZAzg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: david.g.johnston@gmail.com
Subject: Re: How to execute cursor in PostgreSQL?
In-Reply-To: <1425012028153-5839672.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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