Thread: PL/SQL question
Hello everyone,
I try to see if i can make a recursive function with a trigger set on
INSERT and doing an insert under my trigger function.
So i wrote a test function :
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF RECORD AS '
DECLARE
use_t RECORD;
BEGIN
SELECT INTO use_t id_categorie FROM categorie ORDER BY id_categorie
DESC;
IF use_t.id_categorie<>50 THEN
INSERT INTO categorie (nom) VALUES (''test'');
END IF;
RETURN NULL;
END;
'LANGUAGE plpgsql;
The problem is that i can't exec this function to test it, psql return
the following error :
"ERROR: set-valued function called in context that cannot accept a set"
But my INSERT INTO works if i write it directly.
Someone get an idea ?
Thx in advance,
regards,
On Tue, 20 Apr 2004, Froggy / Froggy Corp. wrote:
> I try to see if i can make a recursive function with a trigger set on
> INSERT and doing an insert under my trigger function.
>
> So i wrote a test function :
>
>
> CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF RECORD AS '
> DECLARE
> use_t RECORD;
> BEGIN
>
> SELECT INTO use_t id_categorie FROM categorie ORDER BY id_categorie
> DESC;
> IF use_t.id_categorie<>50 THEN
> INSERT INTO categorie (nom) VALUES (''test'');
> END IF;
>
> RETURN NULL;
>
> END;
> 'LANGUAGE plpgsql;
>
>
> The problem is that i can't exec this function to test it, psql return
> the following error :
>
> "ERROR: set-valued function called in context that cannot accept a set"
Record set returning functions aren't called as:
select foo();
but instead as
select * from foo() AS foo(<columns>);
However, since you're not apparently actually returning a set of anything
in the function you may just want to change the return type.
Hello,
In fact the problem seems to come from the "INSERT INTO". I delete
everything from the function and only keep the "INSERT INTO" and get the
same problem.
Thx in advance for answers,
regards,
Stephan Szabo wrote:
>
> On Tue, 20 Apr 2004, Froggy / Froggy Corp. wrote:
>
> > I try to see if i can make a recursive function with a trigger set on
> > INSERT and doing an insert under my trigger function.
> >
> > So i wrote a test function :
> >
> >
> > CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF RECORD AS '
> > DECLARE
> > use_t RECORD;
> > BEGIN
> >
> > SELECT INTO use_t id_categorie FROM categorie ORDER BY id_categorie
> > DESC;
> > IF use_t.id_categorie<>50 THEN
> > INSERT INTO categorie (nom) VALUES (''test'');
> > END IF;
> >
> > RETURN NULL;
> >
> > END;
> > 'LANGUAGE plpgsql;
> >
> >
> > The problem is that i can't exec this function to test it, psql return
> > the following error :
> >
> > "ERROR: set-valued function called in context that cannot accept a set"
>
> Record set returning functions aren't called as:
> select foo();
> but instead as
> select * from foo() AS foo(<columns>);
>
> However, since you're not apparently actually returning a set of anything
> in the function you may just want to change the return type.
On Wed, 21 Apr 2004, Froggy / Froggy Corp. wrote:
> In fact the problem seems to come from the "INSERT INTO". I delete
> everything from the function and only keep the "INSERT INTO" and get the
> same problem.
A function like:
create function fz1() returns void as '
begin
INSERT INTO categorie (nom) VALUES (''test'');
RETURN;
end;' language 'plpgsql';
seems to work for me, what are you trying precisely?
> In fact the problem seems to come from the "INSERT INTO". I delete > everything from the function and only keep the "INSERT INTO" and get the > same problem. Given that this is supposed to be a trigger function, what's your 'create trigger' statement look like? Part of the problem may be how your 'return null' is being handled, and that can be related to when the trigger fires. -- Mike Nolan
--- Mike Nolan <nolan@gw.tssi.com> wrote: > > In fact the problem seems to come from the > "INSERT INTO". I delete > > everything from the function and only keep the > "INSERT INTO" and get the > > same problem. > > Given that this is supposed to be a trigger > function, what's > your 'create trigger' statement look like? > > Part of the problem may be how your 'return null' is > being handled, AFAIK, returning null from a trigger function causes the whole operation (insert, update or delete) to be aborted, so the transaction is rolled back, including the insert inside the function. You want to return NEW instead. > and that can be related to when the trigger fires. > -- > Mike Nolan > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend __________________________________ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25� http://photos.yahoo.com/ph/print_splash
> AFAIK, returning null from a trigger function causes > the whole operation (insert, update or delete) to be > aborted, so the transaction is rolled back, including > the insert inside the function. You want to return > NEW instead. That's true on a 'before insert' trigger. An 'after insert' trigger can return NULL because the insert that triggered it has already taken place and the value returned by the trigger function is ignored. That's why it was important to ask the original poster what kind of trigger it was 'before insert' or 'after insert'. -- Mike Nolan
I allways get the same error :
ERROR: set-valued function called in context that cannot accept a set
It seems to not accept the "(''test'')". Maybe i made a mistake by
adding the plpgsql langage, but i follow the documentation about that.
If i try to change the insert by "INSERT INTO categorie (id_categorie)
VALUES (''test'');" i got an error msg that "test" is not an integer.
(id_categorie is the primary key of the table).
thx in advance,
regards,
Jeff Eckermann wrote:
>
> --- Mike Nolan <nolan@gw.tssi.com> wrote:
> > > In fact the problem seems to come from the
> > "INSERT INTO". I delete
> > > everything from the function and only keep the
> > "INSERT INTO" and get the
> > > same problem.
> >
> > Given that this is supposed to be a trigger
> > function, what's
> > your 'create trigger' statement look like?
> >
> > Part of the problem may be how your 'return null' is
> > being handled,
>
> AFAIK, returning null from a trigger function causes
> the whole operation (insert, update or delete) to be
> aborted, so the transaction is rolled back, including
> the insert inside the function. You want to return
> NEW instead.
>
> > and that can be related to when the trigger fires.
> > --
> > Mike Nolan
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 8: explain analyze is your friend
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Photos: High-quality 4x6 digital prints for 25¢
> http://photos.yahoo.com/ph/print_splash
The function should be called as "select * from function_name (xyz)" rather than "select from function_name (xyz)", I would guess. Rory On 21/04/04, Froggy / Froggy Corp. (froggy@froggycorp.com) wrote: > I allways get the same error : > > ERROR: set-valued function called in context that cannot accept a set -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>