Re: Getting result from EXECUTE - Mailing list pgsql-general

From Pavel Stehule
Subject Re: Getting result from EXECUTE
Date
Msg-id 162867790709100136p796accabxda8d50c5199aee8@mail.gmail.com
Whole thread Raw
In response to Getting result from EXECUTE  (Robert Fitzpatrick <lists@webtent.net>)
List pgsql-general
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
>

pgsql-general by date:

Previous
From: "Albe Laurenz"
Date:
Subject: Re: Regular expression on a string problem.
Next
From: Stefan Schwarzer
Date:
Subject: Re: Alias "all fields"?