Re: [PG7.4] Using the data from temp table within a function

"Mario Splivalo" <majk(at)fly(dot)srk(dot)fer(dot)hr> wrote in message
news:slrndjl7qd(dot)a82(dot)majk(at)fly(dot)srk(dot)fer(dot)hr(dot)(dot)(dot)
> I've learned that one can't use temporary tables within the function
> unless
> EXECUTE'd the SELECTS from that temp table.
>
> So, I have a function like this:
>
> CREATE FUNCTION Foo1(int4, int4) RETURNS SETOF myType
> AS
> '
> DECLARE
> aDataId ALIAS FOR $1;
> aBid ALIAS FOR $2;
> return myType;
> rec record;
> BEGIN
> CREATE TEMP TABLE tmpTbl
> AS
> SELECT col1, col2 FROM t1 JOIN t2 ON t1.c1 = t2.c3 WHERE t1.c4 = aDataId;
>
> FOR rec IN EXECUTE ''SELECT COUNT(col1) AS num FROM tmpTbl WHERE col2 =
> aBid''
> LOOP
> return.myType = rec.num;
> END LOOP;
>
> RETURN NEXT return;
> RETURN;
> END
> ' language 'pgplsql'
>
>
> Now, when I try to call that function, i get an error that aBid is unknown
> column name. How can I pass the aBid value to the SELECT statement inside
> the EXECUTE?
>
> I'm using temp table because the tables from where to gather the data are
> huge. If I'd be using views instead, it'd take too much time. I tought
> that
> storing only a small fraction of the data (1/1000 of the data is put into
> the temp table), and then performing calculations on that temp table would
> be much faster. I just don't know how to pass parameters to the EXECUTE
> SELECT.
>
> Any help here would be appreciated.
>
> Mike
>
> P.S. I tried above code on pg8.0.3 and 8.1beta2, with same results.
>
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
>
> Mario Splivalo
> msplival(at)jagor(dot)srce(dot)hr

FOR rec IN EXECUTE ''SELECT COUNT(col1) AS num FROM tmpTbl WHERE col2 = " ||
aBid || '' LOOP