Thursday, December 2, 2010

How to get number of rows return by a query using MOVE in Postgresql

0 comments
Create and populate data:
CREATE TABLE emp
(
empno int,
empname varchar(100),
city varchar(100)
);

ALTER TABLE emp
ADD CONSTRAINT empno_pk PRIMARY KEY (empno);

INSERT INTO emp
select q.c as empno, 'testemp_'||q.c as empname, 'testcity_'||q.c as city
FROM
(SELECT generate_series(1,100) as c) AS q
;
Create function:
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS $$
BEGIN
    OPEN $1 FOR
    SELECT  *
    FROM emp
    ;
    RETURN $1;
END;
$$ LANGUAGE plpgsql;
Get number of rows return:
DECLARE
  i INT;
BEGIN;
SELECT reffunc('funccursor');
MOVE ALL IN funccursor;
COMMIT;
Further Study:
http://www.postgresql.org/docs/current/static/sql-move.html

Leave a Reply

Labels