Обсуждение: Cursor Issue??

BEGIN WORK;
DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
FETCH FIRST FROM cursor1;
CLOSE cursor1;
COMMIT WORK;

------------------------
Query result with 1 rows discarded.
Query returned successfully with no result in 31 ms.

In the "data output" view nothing is returned?

Here is a further test:

------------------------------------------------------
BEGIN WORK;
DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
FETCH FIRST IN cursor1;
INSERT INTO partstemp VALUES (PARTS.QTY, PARTS.LENGTH, PARTS.WIDTH);
CLOSE cursor1;
COMMIT WORK;
------------------------------------------------------

What I get is the whole table put into partstemp.  I just wanted the
one row???
One more question what is the difference between using the term IN vs.
FROM?

# bradbudge@hotmail.com / 2005-07-26 14:06:34 -0700:
> BEGIN WORK;
> DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
> FETCH FIRST FROM cursor1;
> CLOSE cursor1;
> COMMIT WORK;
>
> ------------------------
> Query result with 1 rows discarded.
> Query returned successfully with no result in 31 ms.
>
> In the "data output" view nothing is returned?

    Complain to your '"data output" view' vendor.

    test=# create table parts (id serial, t text);
    CREATE TABLE
    test=# insert into parts (t) values ('aaa');
    INSERT 72423 1
    test=# insert into parts (t) values ('bbb');
    INSERT 72424 1
    test=# insert into parts (t) values ('ccc');
    INSERT 72425 1
    test=# select * from parts;
     id |  t
    ----+-----
      1 | aaa
      2 | bbb
      3 | ccc
    (3 rows)

    test=# BEGIN WORK;
    BEGIN
    test=# DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
    DECLARE CURSOR
    test=# FETCH FIRST FROM cursor1;
     id |  t
    ----+-----
      1 | aaa
    (1 row)

    test=# CLOSE cursor1;
    CLOSE CURSOR
    test=# COMMIT WORK;
    COMMIT
    test=#

    As you can see, the fetched row is displayed just fine.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

I found using the shell works but using the phAdminIII GUI is the one
that gives me problems.  I've even tried running it on EMS PostgreSQL
Manager 3.  Same results.  Is this normal?

Got a couple more questions regarding cursors.
1. When I try to run this statement (Declare curs1 refcursor;)  I get
an error ERROR:  syntax error at or near "refcursor" at character 23
2. Using Fetch count; also barfs on me.
3.  After I use Fetch First how do I take the contents and insert them
into another table?
  This is my guess.
  FETCH FIRST IN cursor1 INTO partstemp(qty, length, width)

BEGIN;
DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
FETCH FIRST IN cursor1 INTO partstemp(qty, length, width);    << My
guess but doesnt work.
CLOSE cursor1;
COMMIT;

# bradbudge@hotmail.com / 2005-07-27 12:21:34 -0700:
> I found using the shell works but using the phAdminIII GUI is the one
> that gives me problems.  I've even tried running it on EMS PostgreSQL
> Manager 3.  Same results.  Is this normal?
>
> Got a couple more questions regarding cursors.
> 1. When I try to run this statement (Declare curs1 refcursor;)  I get
> an error ERROR:  syntax error at or near "refcursor" at character 23

    Have you seen the answer to this I sent you in my reply to your
    offlist email? If not, reread the mail. If yes, and you still don't
    see the problem: which part of

    DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
    CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
    [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

    does allow for "DECLARE curs1 refcursor"? The answer is simple:
    none. You're trying to use a plpgsql declaration outside plpgsql.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

# bradbudge@hotmail.com / 2005-08-02 10:01:34 -0400:
> I made it happen in MicrosoftSQL using the first code below.  The only
> difference is I had to create variables.  Which I'm having a hard time
> trying to replicate it in psql.
>
> __________Microsoft Code___________
> USE test
> GO
> DECLARE @qty INT, @Length varchar(20), @Width varchar(40)
> DECLARE cursor1 SCROLL CURSOR FOR
> SELECT * from Parts
> OPEN cursor1
>  FETCH FIRST FROM cursor1
>  INTO @qty, @Length, @Width
>  INSERT INTO PartsTemp (qty, Length, Width)
>     VALUES (@qty, @Length, @Width)
> CLOSE cursor1
> DEALLOCATE cursor1
> GO

    The code above puts a single, randomly chosen tuple from Parts into
    PartsTemp. If that's all you need, you can do it with:

    INSERT INTO PartsTemp (SELECT * FROM Parts LIMIT 1);

> __________psql Code___________
> (These declaration of vaiables don't work)
> DECLARE c_qty INT;
> DECLARE c_Length FLOAT;
> DECLARE c_Width FLOAT;
>
> BEGIN;
> DECLARE cursor1 CURSOR FOR SELECT * FROM Parts;
> FETCH FIRST FROM cursor1 INTO c_qty, c_Length, c_Width;
> INSERT INTO partstemp VALUES (c_qty, c_Length, c_Width);
> CLOSE cursor1;
> COMMIT;
>
> Got any ideas using variable to transfer singular rows?

    If you need to do more (you aren't telling much), and want/need to
    use cursors, you'll have to resort to using PL/pgSQL. This hack
    would do it:

    CREATE TABLE t1 (t1i INT, t1c CHAR(1));
    CREATE TABLE t2 (t2i INT, t2c CHAR(1));

    INSERT INTO t1 VALUES (1, 'a');
    INSERT INTO t1 VALUES (2, 'b');
    INSERT INTO t1 VALUES (3, 'c');

    CREATE FUNCTION do_it()
    RETURNS BOOLEAN
    VOLATILE
    LANGUAGE plpgsql
    AS '
      DECLARE
        _ti INTEGER;
        _tc CHAR(1);
        _c1 CURSOR FOR SELECT t1i, t1c FROM t1;
      BEGIN
        OPEN _c1;
        FETCH _c1 INTO _ti, _tc;
        INSERT INTO t2 VALUES (_ti, _tc);
        CLOSE _c1;
        RETURN TRUE;
      END;
    ';

    SELECT do_it();
    DROP FUNCTION do_it();


    But watch out, because PL/pgSQL doesn't provide a way to create
    SCROLLable cursors, FETCH more than one tuple at a time, or FETCH
    orientation.

    I urge you to read about functions and PL/pgSQL in the manual:

    http://www.postgresql.org/docs/current/static/server-programming.html
    http://www.postgresql.org/docs/current/static/plpgsql.html
    http://www.postgresql.org/docs/current/static/sql-createfunction.html

    and note that SQL DECLARE is a different beast from PL/pgSQL DECLARE,
    etc for other statements.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

Thanks Roman for sticking with me on this!
For whatever reason I cannot load another langage, I think it has to do
with recompiling the program and installing all the options.  Not sure
though??  LANGUAGE plpgsql doesn't exist for me.

I still find this cursor limitation wacked.  I find it hard to believe
that nobody else is requiring this curosr funcionality.  Why else have
a cursor?
See the bottom from this link
http://www.postgresql.org/docs/7/interactive/sql-fetch.htm  Even in
there is display of a teaser that it can be done.  Too bad there is no
example.

My objective is to look at each record one at at time from top to
bottom.  I need to take that information in variable form, and run it
through a routine that is in the cursor block, then the end result
needs to end up in another table.  There will be times where I will
also need to scroll forward and backward.

I do understand, even though I don't agree with PostgreSQl approach to
this, that my only alternative is to create a function.  But as you
know getting a function to work in the way I need it to will require
custom functions.  What a pain!

Hey Roman,

Thanks for your reponse's!

I made it happen in MicrosoftSQL using the first code below.  The only
difference is I had to create variables.  Which I'm having a hard time
trying to replicate it in psql.

__________Microsoft Code___________
USE test
GO
DECLARE @qty INT, @Length varchar(20), @Width varchar(40)
DECLARE cursor1 SCROLL CURSOR FOR
SELECT * from Parts
OPEN cursor1
  FETCH FIRST FROM cursor1
  INTO @qty, @Length, @Width
  INSERT INTO PartsTemp (qty, Length, Width)
    VALUES (@qty, @Length, @Width)
CLOSE cursor1
DEALLOCATE cursor1
GO


__________psql Code___________
(These declaration of vaiables don't work)
DECLARE c_qty INT;
DECLARE c_Length FLOAT;
DECLARE c_Width FLOAT;

BEGIN;
DECLARE cursor1 CURSOR FOR SELECT * FROM Parts;
FETCH FIRST FROM cursor1 INTO c_qty, c_Length, c_Width;
INSERT INTO partstemp VALUES (c_qty, c_Length, c_Width);
CLOSE cursor1;
COMMIT;

Got any ideas using variable to transfer singular rows?

# bradbudge@hotmail.com / 2005-08-04 09:56:03 -0700:
> Thanks Roman for sticking with me on this!
> For whatever reason I cannot load another langage, I think it has to do
> with recompiling the program and installing all the options.  Not sure
> though??  LANGUAGE plpgsql doesn't exist for me.

    Please send the output of this command:

    createlang -U postgres -d $dbname plpgsql

> I still find this cursor limitation wacked.  I find it hard to believe
> that nobody else is requiring this curosr funcionality.  Why else have
> a cursor?

    You are trying to use a cursor in interactive SQL. SQL99 doesn't
    allow that at all!

> See the bottom from this link
> http://www.postgresql.org/docs/7/interactive/sql-fetch.htm  Even in
> there is display of a teaser that it can be done.  Too bad there is no
> example.

    It says:

    : Compatibility
    : SQL92
    :
    :     Note: The non-embedded use of cursors is a Postgres extension.
    :     The syntax and usage of cursors is being compared against the
    :     embedded form of cursors defined in SQL92.
    :
    : SQL92 allows absolute positioning of the cursor for FETCH, and
    : allows placing the results into explicit variables.
    :
    : FETCH ABSOLUTE #
    :      FROM cursor
    :      INTO :variable [, ...]

    The text above is valid for SQL:1999 as well.

    You fall in the non-embedded category, IOW that insufficient
    functionality you are trying to use is outside the SQL standard.

    The example servers to document the paragraph that begins "SQL92
    allows (...)", IOW what you see is the SQL92, Embedded SQL syntax,
    not what you can do in PostgreSQL.

> My objective is to look at each record one at at time from top to
> bottom.  I need to take that information in variable form, and run it
> through a routine that is in the cursor block, then the end result
> needs to end up in another table.  There will be times where I will
> also need to scroll forward and backward.

    To me the fact that the interactive FETCH can only *display* the
    row, while PL/PGSQL is always NO SCROLL and you can only FETCH NEXT
    on it, looks like a bad combination of features and shortcomings.

    Maybe you'd like to take this to the pgsql-docs@ list? At least
    the more knowledgable people could add some real life (= nontrivial)
    examples.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991