PostgreSQL – How to Select Next or First Row Efficiently

If the results are not meant to be used in a subquery but by code, you may use a REFCURSOR in a transaction.

Example:

CREATE FUNCTION example_cursor() RETURNS refcursor AS $$
DECLARE
  c refcursor;
BEGIN
  c:='mycursorname';
  OPEN c FOR select * from generate_series(1,100000);
  return c;                                       
end;
$$ language plpgsql;

Usage for the caller:

BEGIN;
SELECT example_cursor();
 [output: mycursor]
FETCH 10 FROM mycursor;

 Output:

 generate_series 
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10

CLOSE mycursor;
END;

When not interested in piecemeal retrieval, FETCH ALL FROM cursorname may also be used to stream all results to the caller in one step.

About 4.5 years ago, I wrote a rather aggressive stored procedure to traverse data stored in a hierarchy and bring back all descendants within that table : Find highest level of a hierarchical field: with vs without CTEs

I took the code from the GetFamilyTree function and wrote it as a procedure for you

STORED PROCEDURE

DELIMITER $$
DROP PROCEDURE IF EXISTS GetDataFromData $$
CREATE PROCEDURE GetDataFromData (GivenData VARCHAR(255))
ThisStoredProcedure:BEGIN

    DECLARE rv,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';

    DROP TABLE IF EXISTS DataFromData;
    CREATE TEMPORARY TABLE DataFromData LIKE mytable;

    SELECT COUNT(1) INTO @FoundCount FROM mytable WHERE data = GivenData;

    IF @FoundCount = 1 THEN
        SELECT id INTO @FoundID FROM mytable WHERE data = GivenData;

        SET rv = @FoundID;
        SET queue = @FoundID;
        SET queue_length = 1;

        WHILE queue_length > 0 DO
            SET front_id = FORMAT(queue,0);
            IF queue_length = 1 THEN
                SET queue = '';
            ELSE
                SET pos = LOCATE(',',queue) + 1;
                SET q = SUBSTR(queue,pos);
                SET queue = q;
            END IF;
            SET queue_length = queue_length - 1;

            SELECT IFNULL(qc,'') INTO queue_children FROM
            (SELECT GROUP_CONCAT(otherid) qc FROM mytable WHERE id = front_id) A;

            IF LENGTH(queue_children) = 0 THEN
                IF LENGTH(queue) = 0 THEN
                    SET queue_length = 0;
                END IF;
            ELSE
                IF LENGTH(rv) = 0 THEN
                    SET rv = queue_children;
                ELSE
                    SET rv = CONCAT(rv,',',queue_children);
                END IF;
                IF LENGTH(queue) = 0 THEN
                    SET queue = queue_children;
                ELSE
                    SET queue = CONCAT(queue,',',queue_children);
                END IF;
                SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
            END IF;
        END WHILE;

        SET @sql = CONCAT('INSERT INTO DataFromData SELECT * FROM mytable WHERE id in (',rv,')');
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    END IF;

    SELECT * FROM DataFromData;

END $$
DELIMITER ;

SAMPLE DATA FROM YOUR QUESTION

DROP DATABASE IF EXISTS magistermundus;
CREATE DATABASE magistermundus;
USE magistermundus
CREATE TABLE IF NOT EXISTS `mytable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `data` VARCHAR(255),
    `otherid` int(11),
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO mytable (data,otherid) VALUES ('stuff',2);
INSERT INTO mytable (data,otherid) VALUES ('x',4);
INSERT INTO mytable (data,otherid) VALUES ('y',NULL);
INSERT INTO mytable (data,otherid) VALUES ('z',NULL);
SELECT * FROM mytable;

SAMPLE DATA FROM YOUR QUESTION LOADED

mysql> DROP DATABASE IF EXISTS magistermundus;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE magistermundus;
Query OK, 1 row affected (0.01 sec)

mysql> USE magistermundus
Database changed
mysql> CREATE TABLE IF NOT EXISTS `mytable` (
    ->     `id` int(11) NOT NULL AUTO_INCREMENT,
    ->     `data` VARCHAR(255),
    ->     `otherid` int(11),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('stuff',2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('x',4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('y',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('z',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mytable;
+----+-------+---------+
| id | data  | otherid |
+----+-------+---------+
|  1 | stuff |       2 |
|  2 | x     |       4 |
|  3 | y     |    NULL |
|  4 | z     |    NULL |
+----+-------+---------+
4 rows in set (0.00 sec)

mysql>

STORED PROCEDURE EXECUTED

mysql> CALL GetDataFromData('stuff');
+----+-------+---------+
| id | data  | otherid |
+----+-------+---------+
|  1 | stuff |       2 |
|  2 | x     |       4 |
|  4 | z     |    NULL |
+----+-------+---------+
3 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

CAVEAT # 1

Writing this using UNION SELECT was not practical since the depth of the tree of data is not known. This code will handle any depth of data.

CAVEAT # 2

This code does not handle circular references in data (Where (id,otherid) and (otherid,id) coexist in this table).