By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,799 Members | 1,388 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,799 IT Pros & Developers. It's quick & easy.

PL/SQL question

P: n/a
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,

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
Share this Question
Share on Google+
16 Replies

P: n/a
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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2

P: n/a
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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3

P: n/a
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.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4

P: n/a
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.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #5

P: n/a

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?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #6

P: n/a

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?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #7

P: n/a
> 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #8

P: n/a
> 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #9

P: n/a
--- Mike Nolan <no***@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #10

P: n/a
--- Mike Nolan <no***@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #11

P: n/a
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 <no***@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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #12

P: n/a
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 <no***@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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #13

P: n/a
> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #14

P: n/a
> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #15

P: n/a
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. (fr****@froggycorp.com) wrote:
I allways get the same error :

ERROR: set-valued function called in context that cannot accept a set

--
Rory Campbell-Lange
<ro**@campbell-lange.net>
<www.campbell-lange.net>

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #16

P: n/a
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. (fr****@froggycorp.com) wrote:
I allways get the same error :

ERROR: set-valued function called in context that cannot accept a set

--
Rory Campbell-Lange
<ro**@campbell-lange.net>
<www.campbell-lange.net>

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.