pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Return cursor
6+ messages / 3 participants
[nested] [flat]

* Return cursor
@ 2001-05-23 18:20 Alla <alla@sergey.com>
  2001-05-23 23:19 ` Re: Return cursor Alexander Dederer <dederer@spb.cityline.ru>
  2001-05-24 09:39 ` Re: Return cursor Sergey E. Volkov <sve@raiden.bancorp.ru>
  0 siblings, 2 replies; 6+ messages in thread

From: alla@sergey.com @ 2001-05-23 18:20 UTC (permalink / raw)
  To: pgsql-general@postgresql.org.pgsql-sql

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

Thanks a lot in advance for any help you can provide


/****************************************************************************/
                           Alla Gribov 
                          alla@sergey.com
The greatest programming project of all took six days. On the seventh 
day the programmer rested. We've been trying to debug the thing ever 
since. Moral: design before you implement.
/****************************************************************************/



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Return cursor
  2001-05-23 18:20 Return cursor Alla <alla@sergey.com>
@ 2001-05-23 23:19 ` Alexander Dederer <dederer@spb.cityline.ru>
  2001-05-24 12:27   ` Re: Return cursor Alla <alla@sergey.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Alexander Dederer @ 2001-05-23 23:19 UTC (permalink / raw)
  To: pgsql-general@postgresql.org.pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Return cursor
  2001-05-23 18:20 Return cursor Alla <alla@sergey.com>
  2001-05-23 23:19 ` Re: Return cursor Alexander Dederer <dederer@spb.cityline.ru>
@ 2001-05-24 12:27   ` Alla <alla@sergey.com>
  2001-05-24 12:50     ` Re: Return cursor Sergey E. Volkov <sve@raiden.bancorp.ru>
  0 siblings, 1 reply; 6+ messages in thread

From: alla@sergey.com @ 2001-05-24 12:27 UTC (permalink / raw)
  To: pgsql-general@postgresql.org.pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Return cursor
  2001-05-23 18:20 Return cursor Alla <alla@sergey.com>
  2001-05-23 23:19 ` Re: Return cursor Alexander Dederer <dederer@spb.cityline.ru>
  2001-05-24 12:27   ` Re: Return cursor Alla <alla@sergey.com>
@ 2001-05-24 12:50     ` Sergey E. Volkov <sve@raiden.bancorp.ru>
  2001-05-25 14:32       ` Re: Return cursor Alla <alla@sergey.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Sergey E. Volkov @ 2001-05-24 12:50 UTC (permalink / raw)
  To: pgsql-general@postgresql.org.pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Return cursor
  2001-05-23 18:20 Return cursor Alla <alla@sergey.com>
  2001-05-23 23:19 ` Re: Return cursor Alexander Dederer <dederer@spb.cityline.ru>
  2001-05-24 12:27   ` Re: Return cursor Alla <alla@sergey.com>
  2001-05-24 12:50     ` Re: Return cursor Sergey E. Volkov <sve@raiden.bancorp.ru>
@ 2001-05-25 14:32       ` Alla <alla@sergey.com>
  0 siblings, 0 replies; 6+ messages in thread

From: alla@sergey.com @ 2001-05-25 14:32 UTC (permalink / raw)
  To: pgsql-general@postgresql.org.pgsql-sql

"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 :-((



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Return cursor
  2001-05-23 18:20 Return cursor Alla <alla@sergey.com>
@ 2001-05-24 09:39 ` Sergey E. Volkov <sve@raiden.bancorp.ru>
  1 sibling, 0 replies; 6+ messages in thread

From: Sergey E. Volkov @ 2001-05-24 09:39 UTC (permalink / raw)
  To: pgsql-general@postgresql.org.pgsql-sql

Hi Alla,

"Alla" <alla@sergey.com> ???????/???????? ? ???????? ?????????:
news:9275d56e.0105231020.6bc24751@posting.google.com...
> 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

Hmm ... As I know ( fix mee ) Oracle's functions ( procedures ) never return
result sets but ref cursor.

>
> Is there anyway to do the same thing in PostgreSQL?

PostgreSQL doesn't allow to return cursors from stored procedures.
If you want to deal with cursor, just open it in your application.

( See manuals for syntax )

>
> Please, help. So far I could not find anything
>
> Thanks a lot in advance for any help you can provide
>
>
>
/***************************************************************************
*/
>                            Alla Gribov
>                           alla@sergey.com
> The greatest programming project of all took six days. On the seventh
> day the programmer rested. We've been trying to debug the thing ever
> since. Moral: design before you implement.
>
/***************************************************************************
*/





^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-05-25 14:32 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-23 18:20 Return cursor Alla <alla@sergey.com>
2001-05-23 23:19 ` Alexander Dederer <dederer@spb.cityline.ru>
2001-05-24 12:27   ` Alla <alla@sergey.com>
2001-05-24 12:50     ` Sergey E. Volkov <sve@raiden.bancorp.ru>
2001-05-25 14:32       ` Alla <alla@sergey.com>
2001-05-24 09:39 ` Sergey E. Volkov <sve@raiden.bancorp.ru>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox