Explore our sites

Stack Exchange
tour help
Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

here is another algorithm using cursor but i'm having a hard time fixing its error ...

CREATE OR REPLACE FUNCTION extractstudent()
RETURNS VOID AS 
$BODY$
DECLARE
    studcur SCROLL cursor FOR SELECT fname, lname, mname, address FROM student;
BEGIN    
    open studcur; 

    Loop
    --fetching 1 row at a time
    FETCH First FROM studcur;
    --every row fetched is being inserted to another database on the local site
    --myconT is the name of the connection to the other database in the local site
    execute 'SELECT * from dblink_exec(''myconT'', ''insert into temp_student values(studcur)'')';
    --move to the next row and execute again
    move next from studcur;
    --exit when the row content is already empty
    exit when studcur is null;
    end loop;

    close studcur;    

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION extractstudent() OWNER TO postgres;
share|improve this question
add comment

2 Answers

up vote 2 down vote

You rarely need to explicitly use cursors in postgresql or pl/pgsql. What you've written looks suspiciously like a SQL Server cursor loop construct, and you don't need to do that. Also, you can use "PERFORM" instead of "EXECUTE" to run a query and discard the results: this will avoid re-parsing the query each time (although it can't avoid dblink parsing the query each time).

You can do something more like this:

DECLARE
  rec student%rowtype;
BEGIN
  FOR rec IN SELECT * FROM student
  LOOP
    PERFORM dblink_exec('myconT',
      'insert into temp_student values ('
          || quote_nullable(rec.fname) || ','
          || quote_nullable(rec.lname) || ','
          || quote_nullable(rec.mname) || ','
          || quote_nullable(rec.address) || ')');
  END LOOP;
END;
share|improve this answer
 
sorry but i cant really understand the student%rowtype and the part -> (' || quote_nullable(rec.fname) || ',' || quote_nullable(rec.lname) || ',' || quote_nullable(rec.mname) || ',' || quote_nullable(rec.address) || ') could you please explain this part? –  mi ho Mar 18 '11 at 12:35
 
student%rowtype is an automatically-created record (struct) type with the same format as the student table. quote_nullable takes a string value and outputs the quoted value, suitable for including in an SQL statement. –  araqnid Mar 18 '11 at 15:49
add comment
up vote -2 down vote

Why not try it by yourself , according the error, you can try to solve them step by step !

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.