plpgsql and for with integer loopvar error

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: Raw Message | Whole Thread | 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

Responses

Browse pgsql-sql by date

  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