Thread: 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 19026
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.
> 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 19026
Bruce 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