pl/pgsql Composite Parameter Question
Dear all,
Because pl/pgsql accepts at most 16 parameters, I am
trying to feed pl/pgsql function with the whole
record as its parameter.
"Example 24-4. A PL/pgSQL Function on Composite Type"
says I am allowed to do that but I get the error:
mydb=# insert into test values('a');
ERROR: NEW used in non-rule query
What key points have I missed? Please!
CN
===========================
CREATE TABLE test(c1 TEXT);
--test1() will be called not only by insert event.
Thus, it is here:
CREATE FUNCTION test1(test) RETURNS BOOL AS '
BEGIN
InRec ALIAS FOR $1;
RAISE NOTICE ''%'',InRec.c1;
RETURN TRUE;
END;' LANGUAGE 'plpgsql';
CREATE FUNCTION tftest() RETURNS OPAQUE AS '
BEGIN
PERFORM test1(NEW);
RETURN NEW;
END;' LANGUAGE 'plpgsql';
CREATE TRIGGER TriggerTest AFTER INSERT ON test FOR
EACH ROW EXECUTE PROCEDURE tftest();
--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com
<cnliou@eurosport.com> writes:
Because pl/pgsql accepts at most 16 parameters, I am
trying to feed pl/pgsql function with the whole
record as its parameter.
Looks like a bug to me :-(. Unfortunately, there's no time to do
anything about it for 7.2. In the meantime, the 16-parameter limit
is by no means graven in stone; perhaps you could cope for awhile
by recompiling with a larger FUNC_MAX_ARGS.
regards, tom lane
On Mon, Jan 21, 2002 at 04:19:36AM +0000, cnliou@eurosport.com wrote:
BEGIN
InRec ALIAS FOR $1;
RAISE NOTICE ''%'',InRec.c1;
RETURN TRUE;
END;' LANGUAGE 'plpgsql';
Correct would be:
DECLARE
InRec ALIAS FOR $1;
BEGIN
RAISE NOTICE ''%'',InRec.c1;
RETURN TRUE;
END;' LANGUAGE 'plpgsql';
--
Holger Krug
hkrug@rationalizer.com
Tom Lane wrote:
<cnliou@eurosport.com> writes:
Because pl/pgsql accepts at most 16 parameters, I am
trying to feed pl/pgsql function with the whole
record as its parameter.Looks like a bug to me :-(. Unfortunately, there's no time to do
anything about it for 7.2. In the meantime, the 16-parameter limit
is by no means graven in stone; perhaps you could cope for awhile
by recompiling with a larger FUNC_MAX_ARGS.
Tom, can you summarize the issue here?. Our 16-param limit is for both
old and new-style functions? Did we agree to increase this, perhaps to
24 or 32. Did we decide?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Bruce Momjian <pgman@candle.pha.pa.us> writes:
Tom Lane wrote:
Looks like a bug to me :-(. Unfortunately, there's no time to do
anything about it for 7.2. In the meantime, the 16-parameter limit
is by no means graven in stone; perhaps you could cope for awhile
by recompiling with a larger FUNC_MAX_ARGS.
Tom, can you summarize the issue here?
The issue for our TODO is that plpgsql doesn't work very well with
composite (rowtype) parameters.
Our 16-param limit is for both
old and new-style functions? Did we agree to increase this, perhaps to
24 or 32. Did we decide?
I don't recall any consensus in favor of changing the default value of
FUNC_MAX_ARGS. It's already twice what it used to be.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Tom Lane wrote:
Looks like a bug to me :-(. Unfortunately, there's no time to do
anything about it for 7.2. In the meantime, the 16-parameter limit
is by no means graven in stone; perhaps you could cope for awhile
by recompiling with a larger FUNC_MAX_ARGS.Tom, can you summarize the issue here?
The issue for our TODO is that plpgsql doesn't work very well with
composite (rowtype) parameters.
I am confused how this relates to the 16-parameter limit mentioned in
the message. Is it limited to 16 columns of a composite type?
Our 16-param limit is for both
old and new-style functions? Did we agree to increase this, perhaps to
24 or 32. Did we decide?I don't recall any consensus in favor of changing the default value of
FUNC_MAX_ARGS. It's already twice what it used to be.
I do remember a discussion. Certain heavy users are using
object-oriented programming routines and 16 is too small. I know of at
least two big users, Ben Adida (OpenACS), and Josh Berkus, who would
like the limit increased. They can increase it themselves, but because
they distribute source to others, all installs then have to have the
modification. I think they would be happy with 24.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Bruce Momjian <pgman@candle.pha.pa.us> writes:
I am confused how this relates to the 16-parameter limit mentioned in
the message.
It is not related. I had suggested to cnliou that he might avoid
passing a rowtype parameter in favor of passing all the columns
separately --- but he needed more than 16 to do it that way.
regards, tom lane