From: Mario Splivalo <mario.splivalo@mobart.hr>
To: pgsql-sql@postgresql.org
Subject: Sending function parametars within EXECUTE ''SELECT...
Date: Wed, 28 Sep 2005 18:51:09 +0200
Message-ID: <1127926269.16100.3.camel@localhost.localdomain> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mario.splivalo@mobart.hr
Subject: Re: Sending function parametars within EXECUTE ''SELECT...
In-Reply-To: <1127926269.16100.3.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox