"Mario Splivalo" <majk@xxxxxxxxxxxxxx> wrote in message news:slrndjl7qd.a82.majk@xxxxxxxxxxxxxxxxx > 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@xxxxxxxxxxxxx FOR rec IN EXECUTE ''SELECT COUNT(col1) AS num FROM tmpTbl WHERE col2 = " || aBid || '' LOOP ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
