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:

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.

/***************************************************************************
*/

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

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 ' ||

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