pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

plpgsql and for with integer loopvar error
2+ messages / 2 participants
[nested] [flat]

* plpgsql and for with integer loopvar error
@ 2005-01-16 03:43 Ari Kahn <akahn1@gmu.edu>
  2005-01-17 19:38 ` Re: plpgsql and for with integer loopvar error Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Ari Kahn @ 2005-01-16 03:43 UTC (permalink / raw)
  To: 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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: plpgsql and for with integer loopvar error
  2005-01-16 03:43 plpgsql and for with integer loopvar error Ari Kahn <akahn1@gmu.edu>
@ 2005-01-17 19:38 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Stephan Szabo @ 2005-01-17 19:38 UTC (permalink / raw)
  To: Ari Kahn <akahn1@gmu.edu>; +Cc: pgsql-sql


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?



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2005-01-17 19:38 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-16 03:43 plpgsql and for with integer loopvar error Ari Kahn <akahn1@gmu.edu>
2005-01-17 19:38 ` Stephan Szabo <sszabo@megazone.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox