Re: Getting result from EXECUTE

On 9/8/07, Robert Fitzpatrick <lists(at)webtent(dot)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