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

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@jagor.srce.hr

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

Mario Splivalo wrote:

> 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?

You need to construct a dynamic SELECT query - plpgsql cannot interpolate
aBid into your select statement. Try this:

CREATE FUNCTION Foo1(int4, int4) RETURNS SETOF myType
AS
$MYQUOTE$
DECLARE
aDataId ALIAS FOR $1;
aBid ALIAS FOR $2;
return myType;
rec record;

-- create an SQL query
select_sql := 'SELECT COUNT(col1) AS num FROM tmpTbl WHERE col2 = '
|| aBid;
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_sql
LOOP
return.myType = rec.num;
RETURN NEXT return;
END LOOP;

RETURN;
END
$MYQUOTE$ language 'pgplsql'

Couple of things to note:
- get used to using the dollar-quoting mechanism, it will save you *LOTS* of
quoting headaches (see how the select_sql requires only single quotes?)

- I see that your function returns "SETOF myType", however with your
original definition, you have RETURN NEXT *outside* your FOR ... LOOP.
Doing that means you are returning the LAST record from your LOOP - that's
why I've moved it inside the loop. Doing it that way adds each successive
'return' record to the result set - meaning the function will return ALL
records processed by the LOOP.

Hope this helps.

Cheers,

- Brendon.