----- glextact@xxxxxxxxx wrote: > Hi-- > > > I'm having a bit of trouble with the EXECUTE statement for the > following function. > > > I saw a lot of traffic re. plpgsql & variable substitution for 8.4, > but I'm convinced this is something simple (newbie): > > > > CREATE FUNCTION topmixtot (int[]) RETURNS SETOF record AS $$ > DECLARE > gids ALIAS FOR $1; > tmpcnt int[]; > totalcnt integer; > row topmixtot%ROWTYPE; > BEGIN > IF (gids IS NULL) THEN > tmpcnt := array(SELECT sum(t2.cnt) FROM inst_grant t1, > topics_given_grant t2 where t1.gid=t2.gid); > ELSE > tmpcnt := array(SELECT sum(cnt) FROM topics_given_grant WHERE gid = > ANY ( gids )); > END IF; > totalcnt := tmpcnt[0]; > FOR row IN EXECUTE 'SELECT topics_given_grant.tid, ( 100 * (sum(cnt) / > totalcnt ))::numeric as sumcnt FROM topics_given_grant, inst_grant 'SELECT topics_given_grant.tid, ( 100 * (sum(cnt) /'||quote_literal(totalcnt)|| '))::numeric as sumcnt FROM topics_given_grant, inst_grant See here for more detail: http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > WHERE inst_grant.gid=topics_given_grant.gid group by > topics_given_grant.tid order by sumcnt DESC' LOOP > RETURN NEXT row; > END LOOP; > END; > $$ LANGUAGE plpgsql; > > > ERROR msg.: > > > > > db=# select count(*) from topmixtot('{5}') AS foo(tid int, cnt int); > ERROR: column "totalcnt" does not exist > LINE 1: ...SELECT topics_given_grant.tid, ( 100 * (sum(cnt) / totalcnt > )... > > > QUERY: SELECT topics_given_grant.tid, ( 100 * (sum(cnt) / totalcnt > ))::numeric as sumcnt FROM topics_given_grant, nih_grant WHERE > nih_grant.gid=topics_given_grant.gid group by topics_given_grant.tid > order by sumcnt DESC > CONTEXT: PL/pgSQL function "topmixtot" line 13 at FOR over EXECUTE > statement > > > How do I substitute in the value for 'totalcnt' in the EXECUTE SELECT > statement? > > > Thanks, > > > Gerry Adrian Klaver aklaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
