Thread
-
Return cursor
Alla <alla@sergey.com> — 2001-05-23T18:20:14Z
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. /****************************************************************************/ -
Re: Return cursor
Alexander Dederer <dederer@spb.cityline.ru> — 2001-05-23T23:19:44Z
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
-
Re: Return cursor
Sergey E. Volkov <sve@raiden.bancorp.ru> — 2001-05-24T09:39:54Z
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. > /*************************************************************************** */
-
Re: Return cursor
Alla <alla@sergey.com> — 2001-05-24T12:27:27Z
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 -
Re: Return cursor
Sergey E. Volkov <sve@raiden.bancorp.ru> — 2001-05-24T12:50:40Z
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 -
Re: Return cursor
Alla <alla@sergey.com> — 2001-05-25T14:32:47Z
"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 :-((