|
|
 | | From: | Ari Kahn | | Subject: | plpgsql and for with integer loopvar error | | Date: | Sat, 15 Jan 2005 22:43:10 -0500 |
|
|
 | 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
|
|
| | From: | Stephan Szabo | | Subject: | Re: plpgsql and for with integer loopvar error | | Date: | Mon, 17 Jan 2005 11:38:55 -0800 (PST) |
|
|
|
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)
or
count(*) 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?
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
|
|
