PL/SQL question

[Froggy / Froggy Corp.]

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

[Stephan Szabo]

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

[Stephan Szabo]

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

[Froggy / Froggy Corp.]

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

[Froggy / Froggy Corp.]

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

[Stephan Szabo]

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

[Stephan Szabo]

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

[Mike Nolan]

> 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

[Mike Nolan]

> 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

[Jeff Eckermann]

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

[Jeff Eckermann]

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

[Froggy / Froggy Corp.]

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

[Froggy / Froggy Corp.]

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

[Mike Nolan]

> 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

[Mike Nolan]

> 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

[Rory Campbell-Lange]

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

[Rory Campbell-Lange]

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