| 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 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 |
