| From: | Ari Kahn <akahn1(at)gmu(dot)edu> | 
|---|---|
| To: | pgsql-sql(at)postgresql(dot)org | 
| Subject: | plpgsql and for with integer loopvar error | 
| Date: | 2005-01-16 03:43:10 | 
| Message-ID: | BDCD90E6-6770-11D9-97FB-000D936D7BC4@gmu.edu | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-sql | 
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | j.random.programmer | 2005-01-16 13:59:36 | Re: SQL design question: null vs. boolean values | 
| Previous Message | Michael Fuhr | 2005-01-16 01:50:36 | Re: I am writing a MS SQL server conversion utility and am having an issue with timestamp |