Hi guys-

I'm trying to get a function to return nothing, so that it won't insert a new
record in the table if the fk_emp_id = 1. In regular triggers for postgres,
I used something like return NULL. I tried that and this will execute no matter
what. I'm interested in blocking an insert from happening! TIA. roboloki

CREATE OR REPLACE FUNCTION trigfunc_insert2() RETURNS trigger AS $$
switch $TG_op {
INSERT {
set param $NEW($1)
spi_exec "select count(fk_emp_id) as foo from pos where fk_emp_id = $param";
if {$foo == 1 } { # what goes here to stop the insert}
}
default {
return OK
}
}
return OK
$$ LANGUAGE pltcl;

CREATE TRIGGER trig_mytab_foo BEFORE INSERT on pos
FOR EACH ROW EXECUTE FUNCTIONtrigfunc_insert2('fk_emp_id');

On Monday, May 16, 2022 at 10:46:49 PM UTC-4, Jim McNamara wrote:
> Hi guys-
>
> I'm trying to get a function to return nothing, so that it won't insert a new
> record in the table if the fk_emp_id = 1. In regular triggers for postgres,
> I used something like return NULL. I tried that and this will execute no matter
> what. I'm interested in blocking an insert from happening! TIA. roboloki
>
> CREATE OR REPLACE FUNCTION trigfunc_insert2() RETURNS trigger AS $$
> switch $TG_op {
> INSERT {
> set param $NEW($1)
> spi_exec "select count(fk_emp_id) as foo from pos where fk_emp_id = $param";
> if {$foo == 1 } { # what goes here to stop the insert}
> }
> default {
> return OK
> }
> }
> return OK
> $$ LANGUAGE pltcl;
>
> CREATE TRIGGER trig_mytab_foo BEFORE INSERT on pos
> FOR EACH ROW EXECUTE FUNCTIONtrigfunc_insert2('fk_emp_id');

Hi guys-

This is an example of doing it in regular postgresql trigger language.

CREATE OR REPLACE FUNCTION test() RETURNS TRIGGER AS $$
BEGIN
if EXISTS (select fk_emp_id from pos where fk_emp_id = new.fk_emp_id ) then
RETURN NULL;
else
RETURN NEW;
end if;
end;
$$ LANGUAGE plpgsql;

CREATE TRIGGER example_trigger BEFORE INSERT ON pos
FOR EACH ROW EXECUTE PROCEDURE test();

THANKS FOR ANY INPUT!
roboloki

On Tuesday, May 17, 2022 at 5:03:25 AM UTC+1, nefario...@gmail.com wrote:
> On Monday, May 16, 2022 at 10:46:49 PM UTC-4, Jim McNamara wrote:
> > Hi guys-
> >
> > I'm trying to get a function to return nothing, so that it won't insert a new
> > record in the table if the fk_emp_id = 1. In regular triggers for postgres,
> > I used something like return NULL. I tried that and this will execute no matter
> > what. I'm interested in blocking an insert from happening! TIA. roboloki
> >
> > CREATE OR REPLACE FUNCTION trigfunc_insert2() RETURNS trigger AS $$
> > switch $TG_op {
> > INSERT {
> > set param $NEW($1)
> > spi_exec "select count(fk_emp_id) as foo from pos where fk_emp_id = $param";
> > if {$foo == 1 } { # what goes here to stop the insert}
> > }
> > default {
> > return OK
> > }
> > }
> > return OK
> > $$ LANGUAGE pltcl;
> >
> > CREATE TRIGGER trig_mytab_foo BEFORE INSERT on pos
> > FOR EACH ROW EXECUTE FUNCTIONtrigfunc_insert2('fk_emp_id');
> Hi guys-
>
> This is an example of doing it in regular postgresql trigger language.
>
> CREATE OR REPLACE FUNCTION test() RETURNS TRIGGER AS $$
> BEGIN
> if EXISTS (select fk_emp_id from pos where fk_emp_id = new.fk_emp_id ) then
> RETURN NULL;
> else
> RETURN NEW;
> end if;
> end;
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER example_trigger BEFORE INSERT ON pos
> FOR EACH ROW EXECUTE PROCEDURE test();
>
> THANKS FOR ANY INPUT!
> roboloki

The pltcl manual page suggests that returning SKIP should be the equivalent to a NULL in plpgsql.

The return value from a trigger function can be one of the strings OK or SKIP, or a list of column name/value pairs. If the return value is OK, the operation (INSERT/UPDATE/DELETE) that fired the trigger will proceed normally.. SKIP tells the trigger manager to silently suppress the operation for this row

Regards,
Bob

On Tuesday, May 17, 2022 at 3:39:01 AM UTC-4, Professor Zarkov wrote:
> On Tuesday, May 17, 2022 at 5:03:25 AM UTC+1, nefario...@gmail.com wrote:
> > On Monday, May 16, 2022 at 10:46:49 PM UTC-4, Jim McNamara wrote:
> > > Hi guys-
> > >
> > > I'm trying to get a function to return nothing, so that it won't insert a new
> > > record in the table if the fk_emp_id = 1. In regular triggers for postgres,
> > > I used something like return NULL. I tried that and this will execute no matter
> > > what. I'm interested in blocking an insert from happening! TIA. roboloki
> > >
> > > CREATE OR REPLACE FUNCTION trigfunc_insert2() RETURNS trigger AS $$
> > > switch $TG_op {
> > > INSERT {
> > > set param $NEW($1)
> > > spi_exec "select count(fk_emp_id) as foo from pos where fk_emp_id = $param";
> > > if {$foo == 1 } { # what goes here to stop the insert}
> > > }
> > > default {
> > > return OK
> > > }
> > > }
> > > return OK
> > > $$ LANGUAGE pltcl;
> > >
> > > CREATE TRIGGER trig_mytab_foo BEFORE INSERT on pos
> > > FOR EACH ROW EXECUTE FUNCTIONtrigfunc_insert2('fk_emp_id');
> > Hi guys-
> >
> > This is an example of doing it in regular postgresql trigger language.
> >
> > CREATE OR REPLACE FUNCTION test() RETURNS TRIGGER AS $$
> > BEGIN
> > if EXISTS (select fk_emp_id from pos where fk_emp_id = new.fk_emp_id ) then
> > RETURN NULL;
> > else
> > RETURN NEW;
> > end if;
> > end;
> > $$ LANGUAGE plpgsql;
> >
> > CREATE TRIGGER example_trigger BEFORE INSERT ON pos
> > FOR EACH ROW EXECUTE PROCEDURE test();
> >
> > THANKS FOR ANY INPUT!
> > roboloki
> The pltcl manual page suggests that returning SKIP should be the equivalent to a NULL in plpgsql.
>
> The return value from a trigger function can be one of the strings OK or SKIP, or a list of column name/value pairs. If the return value is OK, the operation (INSERT/UPDATE/DELETE) that fired the trigger will proceed normally. SKIP tells the trigger manager to silently suppress the operation for this row
>
> Regards,
> Bob
I figured it out instantly when I woke up. I read your response and realize I put:
SKIP instead of return SKIP.
As my co-worker used to say, it was my houdini door.
thanks so much for getting back to me.
jim