Thread: plpgsql and for with integer loopvar error

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;
minmaxrecord;
 

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
FROMseeds2            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

On Sat, 15 Jan 2005, Ari Kahn wrote:

> 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

I think either of(SELECT count(*) FROM cgt1)
orcount(*) from cgt1
should work in practice (I personally like the former because that's the
scalar subquery syntax), however, since you're getting the count into
gt1cnt, why not just use the variable?