From: | glextact(at)gmail(dot)com |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | variable |
Date: | 2009-03-19 17:26:27 |
Message-ID: | 6016210B-7D6F-4E59-841D-4A32C1AA9157@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | ries van Twisk | 2009-03-19 17:28:11 | Re: Special charaters |
Previous Message | Pavel Stehule | 2009-03-19 17:06:14 | Re: deadlock problem |