From: "codeWarrior" <gpatnude@hotmail.com>
Subject: Re: Sending function parametars within EXECUTE ''SELECT...
Date: Wed, 28 Sep 2005 10:01:23 -0700
Organization: Hub.Org Networking Services
Lines: 67
Message-ID: <dhei5n$1fee$1@news.hub.org>
References: <1127926269.16100.3.camel@localhost.localdomain>
To: pgsql-sql@postgresql.org


"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