Обсуждение: Sending function parametars within EXECUTE ''SELECT...
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.
"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
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
> 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/
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."