Thread: Sending function parametars within EXECUTE ''SELECT...

Sending function parametars within EXECUTE ''SELECT...

From
Mario Splivalo
Date:
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.



Re: Sending function parametars within EXECUTE ''SELECT...

From
"codeWarrior"
Date:
"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




Re: Sending function parametars within EXECUTE ''SELECT...

From
Mario Splivalo
Date:
On Wed, 2005-09-28 at 10:01 -0700, codeWarrior wrote:
> >
> > ---------------------------(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
> 

Huh! :) Seems so obvious now :)

Is there a way to assing variable a value returned from query that
accesses the temporary table in a way you explained above?

For instance, I can do:

myValue := col2 FROM tmpTbl WHERE someValue = somethingElse...

But then I encounter the problem with temp tables. Can I do EXECUTE'd
SELECT like you explained above? Or I need to do 'widdleing' with FOR
like explained above? 
       Mario



Re: Sending function parametars within EXECUTE ''SELECT...

From
"Stewart Ben (RBAU/EQS4) *"
Date:
> Is there a way to assing variable a value returned from query that
> accesses the temporary table in a way you explained above?
>
> For instance, I can do:
>
> myValue := col2 FROM tmpTbl WHERE someValue = somethingElse...

If I'm parsing this correctly, all you need do is:

SELECT col2 INTO myValue FROM .......

Best regards,

Ben Stewart

--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:ben.stewart@au.bosch.com
http://www.bosch.com.au/


Re: Sending function parametars within EXECUTE ''SELECT...

From
Mario Splivalo
Date:
On Thu, 2005-09-29 at 16:36 +1000, Stewart Ben (RBAU/EQS4) * wrote:
> > Is there a way to assing variable a value returned from query that
> > accesses the temporary table in a way you explained above?
> > 
> > For instance, I can do:
> > 
> > myValue := col2 FROM tmpTbl WHERE someValue = somethingElse...
> 
> If I'm parsing this correctly, all you need do is:
> 
> SELECT col2 INTO myValue FROM .......
> 

So, when accessing temporary tables, like this:

EXECUTE ''SELECT col2 INTO myValue FROM tmpTable...''

Don't have postsgres handy now, I'll check that ASAP, but somehow I feel
that I'd run into same problems as before. 

I'll try and I'll post a reply here.
Mario
-- 
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."