PL/SQL question

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 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:

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>