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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, May 17, 2022 at 5:03:25 AM UTC+1, [email protected] 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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, [email protected] 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)