Thread: variable

----- glextact@gmail.com 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@comcast.net