Subject: Sending function parametars within EXECUTE ''SELECT...
From: Mario Splivalo <mario.splivalo@mobart.hr>
Reply-To: mario.splivalo@mobart.hr
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Organization: Mob-Art
Date: Wed, 28 Sep 2005 18:51:09 +0200
Message-Id: <1127926269.16100.3.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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.