Thread: greetings

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
returnedthen goto finished;            find position in linked list to insert this information;            goto loop;
        finished:            CLOSE emp_cursor;
 

Ken Kline <ken@oldbs.com> 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

Ian Lance Taylor <ian@airs.com> writes:
> PL/pgSQL does not support cursors.  It also does not support goto.

The context is pretty unclear here, but perhaps he needs ecpg not
plpgsql ... is this to be client- or server-side code?
        regards, tom lane

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

orginally what I wanted to do was this:

INSERT INTO pledge_classes (semester, year)
SELECT distinct pseason, pyear from load_bros
WHERE  pyear is not null
AND    pseason is not null
order by pyear, pseason;

however pgsql does not allow order by in an INSERT-SELECT statement
so i thought maybe I could do something like this:


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


Well, all the code I just showed you works in orcacle but pgsql is a
little different
and even though the book has an example of a cursor
http://www.postgresql.org/docs/aw_pgsql_book/node142.html
it does not explain before hand
1) the format of an anoymous block
2) how to loop a cursor
3) how to reference columns froma cursor row (ie rec.column_name)

thanks

Ken


Tom Lane wrote:

> Ian Lance Taylor <ian@airs.com> writes:
> > PL/pgSQL does not support cursors.  It also does not support goto.
>
> The context is pretty unclear here, but perhaps he needs ecpg not
> plpgsql ... is this to be client- or server-side code?
>
>                         regards, tom lane

Ken Kline <ken@oldbs.com> writes:
> orginally what I wanted to do was this:

> INSERT INTO pledge_classes (semester, year)
> SELECT distinct pseason, pyear from load_bros
> WHERE  pyear is not null
> AND    pseason is not null
> order by pyear, pseason;

> however pgsql does not allow order by in an INSERT-SELECT statement

Three answers for the price of one ;-) :

1. Why are you trying to constrain the order in an INSERT in the first
place?  Tuple order in a table is meaningless under SQL semantics.

2. If you really feel you have to have that, you could rely on the
sorting done implicitly by DISTINCT:
INSERT INTO pledge_classes (year, semester)SELECT distinct pyear, pseason from load_brosWHERE  pyear is not nullAND
pseasonis not null;
 

3. 7.1 will allow you to use an ORDER BY here, pointless though it is.
        regards, tom lane

Ken Kline <ken@oldbs.com> 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
ISNOT 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