Thread: Getting result from EXECUTE
I have a trigger function that I want to apply to several tables, hence my use of TG_RELNAME. I just want the record to get inserted if an UPDATE comes from my view rule if the record for the client doesn't already exist. This is what I have, but I'm finding the FOUND is not returned for EXECUTE. How can I accomplish what I need? CREATE OR REPLACE FUNCTION "public"."create_fldclientnumber_trigg_func" () RETURNS trigger AS' begin EXECUTE ''SELECT * FROM '' || TG_RELNAME || '' WHERE fldclientnumber = '' || NEW.fldclientnumber; IF NOT FOUND THEN EXECUTE ''INSERT INTO '' || TG_RELNAME || '' (fldclientnumber) VALUES ('' || NEW.fldclientnumber || '')''; END IF; RETURN NEW; end; 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; Thanks for the help. -- Robert
On 9/8/07, Robert Fitzpatrick <lists@webtent.net > wrote:
I have a trigger function that I want to apply to several tables, hence
my use of TG_RELNAME. I just want the record to get inserted if an
UPDATE comes from my view rule if the record for the client doesn't
already exist. This is what I have, but I'm finding the FOUND is not
returned for EXECUTE. How can I accomplish what I need?
CREATE OR REPLACE FUNCTION "public"."create_fldclientnumber_trigg_func" () RETURNS trigger AS'
begin
EXECUTE ''SELECT * FROM '' || TG_RELNAME || '' WHERE fldclientnumber = '' || NEW.fldclientnumber;
IF NOT FOUND THEN
EXECUTE ''INSERT INTO '' || TG_RELNAME || '' (fldclientnumber) VALUES ('' || NEW.fldclientnumber || '')'';
END IF;
RETURN NEW;
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Thanks for the help.
--
Robert
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
AFAIK the FOUND variable does not get updated as part of an EXECUTE command.
Consider using a strict INTO clause alongwith EXECUTE. This way a NO_DATA_FOUND exception will be generated if your query did'nt return any data. Something like this:
DECLARE
v_rec record;
BEGIN
EXECUTE ''SELECT * FROM '' || TG_RELNAME || '' WHERE fldclientnumber = '' || NEW.fldclientnumber INTO STRICT v_rec;
EXCEPTION
when no_data_found then
/* do something */
regards,
--
Sibte Abbas
Hello execute doesn't set FOUND variable, but sets diagnostics variables. you can: create table foo(a integer); insert into foo values(10),(20); create or replace function f() returns void as $$declare rc integer; begin execute 'update foo set a = a'; get diagnostics rc = row_count; raise notice '%', rc; end; $$ language plpgsql; postgres=# select f(); NOTICE: 2 f --- (1 row) Regards Pavel Stehule 2007/9/9, Robert Fitzpatrick <lists@webtent.net>: > I have a trigger function that I want to apply to several tables, hence > my use of TG_RELNAME. I just want the record to get inserted if an > UPDATE comes from my view rule if the record for the client doesn't > already exist. This is what I have, but I'm finding the FOUND is not > returned for EXECUTE. How can I accomplish what I need? > > CREATE OR REPLACE FUNCTION "public"."create_fldclientnumber_trigg_func" () RETURNS trigger AS' > begin > EXECUTE ''SELECT * FROM '' || TG_RELNAME || '' WHERE fldclientnumber = '' || NEW.fldclientnumber; > IF NOT FOUND THEN > EXECUTE ''INSERT INTO '' || TG_RELNAME || '' (fldclientnumber) VALUES ('' || NEW.fldclientnumber || '')''; > END IF; > RETURN NEW; > end; > 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; > > Thanks for the help. > > -- > Robert > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >