Re: Cursor Issue?? - Mailing list pgsql-general

# 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