I have a single column result that I want to loop through using an index. I also want to jump ahead in the result using the index (below: i:=i+cluster_cnt-1;) . However, I'm getting the following error.

ERROR: syntax error at or near "SELECT" at character 9
CONTEXT: PL/pgSQL function "gets_nooky" line 14 at for with integer loopvar


Here is my function ...

CREATE FUNCTION gets_nooky() returns numeric AS
'
DECLARE
    i integer;
    gt1cnt record;
    gt1 record;
    cluster record;
    cluster_cnt integer;
    slocus integer;
    minmax record;

BEGIN
    SELECT INTO gt1 * FROM cgt1;
    SELECT count(*) INTO gt1cnt FROM cgt1;

    FOR i IN 1 .. SELECT count(*) FROM cgt1
    LOOP

        SELECT INTO cluster DISTINCT qry
        FROM seeds2
        WHERE seed IN (
            SELECT qry
            FROM seeds2
            WHERE seed=gt1.seed[i]);

        slocus := i;

        SELECT INTO minmax min(qle) AS mqle, max(que) AS mque
        FROM seeds2
        WHERE seed IN (SELECT * FROM cluster);

        UPDATE locus
        SET locus=slocus, lle=minmax.mqle, lue=minmax.mque
        WHERE id IN (
            SELECT *
            FROM cluster
            );

        SELECT INTO cluster_cnt count(*) FROM cluster;
        i:=i+cluster_cnt-1;
        DELETE FROM cluster;

    END LOOP;

    return i;

END;
'
language 'plpgsql';


Any help is much appreciated!

Thanks,
Ari


---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Reply via email to