Re: Sending function parametars within EXECUTE ''SELECT... - Mailing list pgsql-sql

From codeWarrior
Subject Re: Sending function parametars within EXECUTE ''SELECT...
Date
Msg-id dhei5n$1fee$1@news.hub.org
Whole thread Raw
In response to Sending function parametars within EXECUTE ''SELECT...  (Mario Splivalo <mario.splivalo@mobart.hr>)
Responses Re: Sending function parametars within EXECUTE ''SELECT...
List pgsql-sql
"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




pgsql-sql by date:

Previous
From: Mario Splivalo
Date:
Subject: Sending function parametars within EXECUTE ''SELECT...
Next
From: Ian Meyer
Date:
Subject: Problem with function and trigger...