"Mario Splivalo" <mario.splivalo@mobart.hr> wrote in message
news:1127926269.16100.3.camel@localhost.localdomain...
> 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
FOR rec IN EXECUTE ''SELECT COUNT(col1) AS num FROM tmpTbl WHERE col2 = " ||
aBid || '' LOOP
