pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: alla@sergey.com
To: pgsql-general@postgresql.org.pgsql-sql
Subject: Re: Return cursor
Date: 24 May 2001 05:27:27 -0700
Message-ID: <9275d56e.0105240427.2fa28c31@posting.google.com> (raw)
References: <9275d56e.0105231020.6bc24751@posting.google.com>
	<9ehged$k4f$1@news.tht.net>

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



view thread (6+ messages)  latest in thread

Message-ID: <9275d56e.0105240427.2fa28c31@posting.google.com>
Permalink:  ../9275d56e.0105240427.2fa28c31@posting.google.com/
Also on:    postgresql.org/message-id/9275d56e.0105240427.2fa28c31@posting.google.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: alla@sergey.com, pgsql-general@postgresql.org.pgsql-sql
  Subject: Re: Return cursor
  In-Reply-To: <9275d56e.0105240427.2fa28c31@posting.google.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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