Getting result from EXECUTE

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>: