Lists: | pgsql-sql |
---|
From: | <abief_ag_-postgresql(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Trigger calling a function HELP ME! (2) |
Date: | 2004-04-21 15:16:05 |
Message-ID: | 20040421151605.77039.qmail@web13901.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
Sorry. I realize I slipped an error in my code:
the code is:
---
CREATE TABLE public.imp_test
(
id int8,
value text
) WITHOUT OIDS;
CREATE OR REPLACE FUNCTION public.imp_test_to_out_test(imp_test)
RETURNS imp_test AS
'begin
return $1;
end;'
LANGUAGE 'plpgsql' STABLE;
CREATE OR REPLACE FUNCTION public.imp_test_trigger()
RETURNS trigger AS
'begin
return imp_test_to_out_test(new);
end;'
LANGUAGE 'plpgsql' STABLE;
CREATE TRIGGER imp_test_trigger_001
BEFORE INSERT OR UPDATE
ON public.imp_test
FOR EACH ROW
EXECUTE PROCEDURE public.imp_test_trigger();
---
regards,
=====
Riccardo G. Facchini
From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | <abief_ag_-postgresql(at)yahoo(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Trigger calling a function HELP ME! (2) |
Date: | 2004-04-22 11:19:12 |
Message-ID: | 200404221219.12694.dev@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
On Wednesday 21 April 2004 16:16, abief_ag_-postgresql(at)yahoo(dot)com wrote:
> CREATE OR REPLACE FUNCTION public.imp_test_to_out_test(imp_test)
> RETURNS imp_test AS
> 'begin
> return $1;
> end;'
> LANGUAGE 'plpgsql' STABLE;
>
> CREATE OR REPLACE FUNCTION public.imp_test_trigger()
> RETURNS trigger AS
> 'begin
> return imp_test_to_out_test(new);
> end;'
> LANGUAGE 'plpgsql' STABLE;
This is your problem. NEW is a special variable, and I don't think you can
pass it into another function (other than as NEW.col1, NEW.col2, NEW.col3
etc).
You can however use TG_NAME or TG_RELNAME to see what trigger/table called
you. I find that's helpful.
--
Richard Huxton
Archonet Ltd
From: | "Riccardo G(dot) Facchini" <abief_ag_-postgresql(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Trigger calling a function HELP ME! (2) |
Date: | 2004-04-24 05:50:22 |
Message-ID: | 20040424055022.96721.qmail@web13907.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
--- Richard Huxton <dev(at)archonet.com> wrote:
> On Wednesday 21 April 2004 16:16, abief_ag_-postgresql(at)yahoo.com
> wrote:
>
> > CREATE OR REPLACE FUNCTION public.imp_test_to_out_test(imp_test)
> > RETURNS imp_test AS
> > 'begin
> > return $1;
> > end;'
> > LANGUAGE 'plpgsql' STABLE;
> >
> > CREATE OR REPLACE FUNCTION public.imp_test_trigger()
> > RETURNS trigger AS
> > 'begin
> > return imp_test_to_out_test(new);
> > end;'
> > LANGUAGE 'plpgsql' STABLE;
>
> This is your problem. NEW is a special variable, and I don't think
> you can
> pass it into another function (other than as NEW.col1, NEW.col2,
> NEW.col3
> etc).
>
> You can however use TG_NAME or TG_RELNAME to see what trigger/table
> called
> you. I find that's helpful.
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
So, If I have to do a record-wide manipulation (almost all the fields)
to the NEW record just before returning from the trigger, what other
possibility do you suggest?
thanks for your kind answer...
=====
Riccardo G. Facchini
From: | "Riccardo G(dot) Facchini" <abief_ag_-postgresql(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Trigger calling a function HELP ME! (2) |
Date: | 2004-04-26 07:47:13 |
Message-ID: | 20040426074713.32544.qmail@web13906.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
--- Richard Huxton <dev(AT)archonet.com> wrote:
> On Wednesday 21 April 2004 16:16, abief_ag_-postgresql(AT)yahoo.com
> wrote:
>
> > CREATE OR REPLACE FUNCTION public.imp_test_to_out_test(imp_test)
> > RETURNS imp_test AS
> > 'begin
> > return $1;
> > end;'
> > LANGUAGE 'plpgsql' STABLE;
> >
> > CREATE OR REPLACE FUNCTION public.imp_test_trigger()
> > RETURNS trigger AS
> > 'begin
> > return imp_test_to_out_test(new);
> > end;'
> > LANGUAGE 'plpgsql' STABLE;
>
> This is your problem. NEW is a special variable, and I don't think
> you can
> pass it into another function (other than as NEW.col1, NEW.col2,
> NEW.col3
> etc).
>
> You can however use TG_NAME or TG_RELNAME to see what trigger/table
> called
> you. I find that's helpful.
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
One other issue, as I understand it, NEW (and OLD) is a variable of
type record, so you should be able to pass it to a function and get it
as a return value, but it seems that there is a bug reported for
version 7.2.1 that inhibits the use of record as type to be passed to a
function.
any suggestion?
=====
Riccardo G. Facchini