pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger calling a function HELP ME! (2)
5+ messages / 4 participants
[nested] [flat]
* Trigger calling a function HELP ME! (2)
@ 2004-04-21 14:54 Riccardo Facchini <facchini@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Riccardo Facchini @ 2004-04-21 14:54 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Trigger calling a function HELP ME! (2)
@ 2004-04-21 15:16 abief_ag_-postgresql@yahoo.com
0 siblings, 1 reply; 5+ messages in thread
From: abief_ag_-postgresql@yahoo.com @ 2004-04-21 15:16 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger calling a function HELP ME! (2)
@ 2004-04-22 11:19 Richard Huxton <dev@archonet.com>
parent: abief_ag_-postgresql@yahoo.com
0 siblings, 2 replies; 5+ messages in thread
From: Richard Huxton @ 2004-04-22 11:19 UTC (permalink / raw)
To: abief_ag_-postgresql@yahoo.com; pgsql-sql
On Wednesday 21 April 2004 16:16, abief_ag_-postgresql@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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger calling a function HELP ME! (2)
@ 2004-04-24 05:50 Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 5+ messages in thread
From: Riccardo G. Facchini @ 2004-04-24 05:50 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger calling a function HELP ME! (2)
@ 2004-04-26 07:47 Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 5+ messages in thread
From: Riccardo G. Facchini @ 2004-04-26 07:47 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-04-26 07:47 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-21 14:54 Trigger calling a function HELP ME! (2) Riccardo Facchini <facchini@yahoo.com>
2004-04-21 15:16 Trigger calling a function HELP ME! (2) abief_ag_-postgresql@yahoo.com
2004-04-22 11:19 ` Richard Huxton <dev@archonet.com>
2004-04-24 05:50 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-04-26 07:47 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox