Thread: Re: Return cursor

Can you send PL/SQL code and back-end code used this PL/SQL code?
Myself  trubles with CURSOR I resolve use LIMIT ... OFFSET ...

Alla wrote:
> I am porting our database from Oracle to PostgreSQL
>
> I know quite a lot about Oracle and pretty much nothing about
> PostgreSQL :-))
>
> I have a lot of stored procedures in Oracle that return result sets or
> cursor. All I have to do there is open a cursor and calling
> application can just fetch it
>
> Is there anyway to do the same thing in PostgreSQL?
>
> Please, help. So far I could not find anything

If you are planing  to use ecpg :

exec sql declare cuserbase cursor for
     select column1, column2, column3 from my_table order by 1;
exec sql open cuserbase;

exec sql whenever not found do break;
while ( true ) {
   exec sql fetch cuserbase into ...
   // do same work
}

exec sql close cuserbase;

Please read documentation for more detail.

Good luck.

Sergey.

"Alla" <alla@sergey.com> ???????/???????? ? ???????? ?????????:
news:9275d56e.0105240427.2fa28c31@posting.google.com...
> Alexander Dederer <dederer@spb.cityline.ru> wrote in message
news:<9ehged$k4f$1@news.tht.net>...
> > Can you send PL/SQL code and back-end code used this PL/SQL code?
> > Myself  trubles with CURSOR I resolve use LIMIT ... OFFSET ...
>
> It would look something like this:
>
> create or replace package my_package
> AS
>    type gc_cursor is ref cursor;
>
>    procedure load_users
>    (pp_user_base     out   gc_cursor);
> end my_package;
> /
>
> create or replace package body my_package
> as
> procedure load_users
> (pp_user_base     out   gc_cursor)
> as
> begin
>    open pp_user_base for
>       select column1, column2, column3
>       from my_table
>       order by 1;
>
> exception
>    when others then
>       raise_application_error(-20100, 'Error while trying to load user
base ' ||
>          sqlerrm);
> end load_users;
>
> end my_package;
> /
>
> C program would look something like this:
>
> void
> load_user_base()
> {
> EXEC SQL BEGIN DECLARE SECTION;
>    sql_cursor cUserBase;
> EXEC SQL END DECLARE SECTION;
>
>   struct USER_PROFILE {
>     .......
>   } user_profile[ARRAY_LENGTH];
>
>   struct USER_PROFILE_IND {
>     .......
>   } user_profile_ind[ARRAY_LENGTH];
>
>   EXEC SQL ALLOCATE :cUserBase;  /* allocate the cursor variable */
>
>   EXEC SQL EXECUTE
>     BEGIN
>       my_package.load_users(:cUserBase);
>     END;
>   END-EXEC;
>
>   EXEC SQL WHENEVER NOT FOUND DO break;
>
>   for (;;) {
>     EXEC SQL FOR :i FETCH :cUserBase INTO :user_profile:user_profile_ind;
>
>     if (sqlca.sqlcode != 0) {
>       fprintf(stderr, "Fetching users %s\n", sqlca.sqlerrm.sqlerrmc);
>       break;
>     }
>   }
>
> ........
> ........
>
> Thanks for your response
>
> Alla

"Sergey E. Volkov" <sve@raiden.bancorp.ru> wrote in message news:<9ej088$ulc$1@news.tht.net>...
> If you are planing  to use ecpg :
>
> exec sql declare cuserbase cursor for
>      select column1, column2, column3 from my_table order by 1;
> exec sql open cuserbase;

Thanks, but this is exactly what I am trying to avoid - having select statements
in client applications.

I want to write stored procedures with all the logic and give the names
and parameters to my developers, so they could just do the fetch.

In this case, I'll be free to change any underlying logic - tables' names,
column names, select statements itself, add some extra logic to the
where clause etc

Alla

P.S. If you are curious, I found something like this:

create function edf_load_user_base()
returns setof edv_user_base as '
select * from edv_user_base
where ... (complex where clause)'
language 'sql';

And then call it as following:

select column1(edf_load_user_base()), column2(edf_load_user_base()) etc

Does anybody know how I can return setof of something other than table%rowtype,
i.e. can I define a record type my_record and do something like this:

create function edf_load_user_base()
returns setof my_record
....

Sorry to be a pain, but I have a deadline to do the migration and
not enough time to do my research :-((

Alexander Dederer <dederer@spb.cityline.ru> wrote in message news:<9ehged$k4f$1@news.tht.net>...
> Can you send PL/SQL code and back-end code used this PL/SQL code?
> Myself  trubles with CURSOR I resolve use LIMIT ... OFFSET ...

It would look something like this:

create or replace package my_package
AS
   type gc_cursor is ref cursor;

   procedure load_users
   (pp_user_base     out   gc_cursor);
end my_package;
/

create or replace package body my_package
as
procedure load_users
(pp_user_base     out   gc_cursor)
as
begin
   open pp_user_base for
      select column1, column2, column3
      from my_table
      order by 1;

exception
   when others then
      raise_application_error(-20100, 'Error while trying to load user base ' ||
         sqlerrm);
end load_users;

end my_package;
/

C program would look something like this:

void
load_user_base()
{
EXEC SQL BEGIN DECLARE SECTION;
   sql_cursor    cUserBase;
EXEC SQL END DECLARE SECTION;

  struct USER_PROFILE {
    .......
  } user_profile[ARRAY_LENGTH];

  struct USER_PROFILE_IND {
    .......
  } user_profile_ind[ARRAY_LENGTH];

  EXEC SQL ALLOCATE :cUserBase;  /* allocate the cursor variable */

  EXEC SQL EXECUTE
    BEGIN
      my_package.load_users(:cUserBase);
    END;
  END-EXEC;

  EXEC SQL WHENEVER NOT FOUND DO break;

  for (;;) {
    EXEC SQL FOR :i FETCH :cUserBase INTO :user_profile:user_profile_ind;

    if (sqlca.sqlcode != 0) {
      fprintf(stderr, "Fetching users %s\n", sqlca.sqlerrm.sqlerrmc);
      break;
    }
  }

........
........

Thanks for your response

Alla