Re: [SQL] pgsql and cursor

[Ian Lance Taylor]

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

[Ian Lance Taylor]

Ken Kline <[EMAIL PROTECTED]> writes:

> I have just joined the list a few days ago and am trying quite hard
> to come up to speed with pgsql but i find documentaion frustratiing.
> I think maybe it;s just a matter of finding things that are of the
> correct
> scope.  I've been an Oracle developer for over 6 years so often I
> know what it is I want to do but something is just a little different.
> If there are others on the list that learned in Oracle then pgsql
> please tell me what  you think are the best resources.
> 
> Recently I did a google search on the key words "postgresql cursor loop"
> 
> the example below is all  I could come up with but it doesn't seem
> to work is this for an older version or am I just overlooking
> something simple?
> 
> thanks
> 
> Ken
> 
>  DECLARE emp_cursor CURSOR FOR
>  SELECT Salary, Title, Start, Stop
>  FROM Employee;
>  OPEN emp_cursor;
>  loop:
>  FETCH emp_cursor INTO :salary, :start, :stop;
>  if no-data returned then goto finished;
>  find position in linked list to insert this information;
>  goto loop;
>  finished:
>  CLOSE emp_cursor;

PL/pgSQL does not support cursors.  It also does not support goto.

You can write the above as something like (untested):
  FOR emprec IN SELECT Salary, Title, Start, Stop FROM Employee LOOP
IF no-data returned then EXIT;
  END LOOP;

I have a patch adding cursors to PL/pgSQL.  You can find it at
http://www.airs.com/ian/pgsql-cursor.html
This patch will not be in 7.1, but it may be in 7.2.

However, it will not help you with the lack of goto.  You shouldn't
really use goto for a simple program like your example.  But perhaps
you are doing something more complex in your real code.

Ian

Re: [SQL] greetings

[Ian Lance Taylor]

Ken Kline <[EMAIL PROTECTED]> writes:

> it is to be server side code
> the code I gave you was merely an example
> of a cursor that I found when I did a search...
> http://www.armed.net/how/pg001676.htm

I think Tom addressed how to solve your actual problem.

I'll just comment that the example you see is of pure SQL being passed
in from a client.  If you want to use a loop, you need to use
PL/pgSQL.  And PL/pgSQL doesn't currently support cursors.

> DECLARE
> CURSOR get_rows AS
> SELECT DISTINCT pseason, pyear FROM load_members
> WHERE pyear IS NOT NULL
> AND pseason IS NOT NULL
> ORDER BY pyear, pseason;
> BEGIN
> FOR rec IN get rows LOOP
> INSERT INTO pledge_classes (semester, year)
> VALUES
> (rec.pseason, rec.pyear);
> END LOOP;
> COMMIT;
> END;

You can do this in PL/pgSQL by just doing the FOR over the SELECT.
This is untested, but it shows the basic idea.

DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT DISTINCT pseason, pyear FROM load_members
 WHERE pyear IS NOT NULL
 AND pseason IS NOT NULL
 ORDER BY pyear, pseason;
  LOOP
INSERT INTO pledge_classes (semester, year)
VALUES (rec.pseason, rec.pyear);
END LOOP;
COMMIT;
END;

But as Tom said there is no reason to sort records when inserting
them, since it doesn't make any difference what order they are in in
the table.

Ian