Обсуждение: PL/SQL question

Поиск
Список
Период
Сортировка

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,

Re: PL/SQL question

От
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.

Re: PL/SQL question

От
"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.

Re: PL/SQL question

От
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?

Re: PL/SQL question

От
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

Re: PL/SQL question

От
Jeff Eckermann
Дата:
--- 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

Re: PL/SQL question

От
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

Re: PL/SQL question

От
"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 <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

Re: PL/SQL question

От
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. (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>