Skip site navigation (1) Skip section navigation (2)

Re: Problem with FOUND

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "A B" <gentosaker(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Problem with FOUND
Date: 2008-06-27 08:33:07
Message-ID: 162867790806270133r2e38e2fer1e07d070ac29659b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
2008/6/27 A B <gentosaker(at)gmail(dot)com>:
>> I think you'd be well advised to rethink your table layout so you don't
>> need so much dynamic SQL.  The above is going to suck on both
>> performance and readability grounds, and it doesn't look like it's
>> accomplishing anything you couldn't do by combining all the Rating
>> tables into one table with an extra key column.
>
> Yes, it sucks, but I have to live with it right now (I've also removed
> a lot of code from the function to make it more readable for you)
> There are a lot of other parameters and execute commands :-(
> Since I don't run >=8.2 I cant use   FOR-EXECUTE-UPDATE-RETURNING.
> So I will have to find another way.
>
> But if UPDATE sets FOUND, what is the reason for EXECUTE not to set
> FOUND if the query executed is an UPDATE?
> Is it because it is impossible to tell in advance what kind of query
> an EXECUTE statement will acctually execute?

compatibility with Oracle's PL/SQL. Internally isn't reason for it :(
try GET DIAGNOSTICS

postgres=# create table foox(a integer);
CREATE TABLE
postgres=# insert into foox values(10);
INSERT 0 1

postgres=# create function gg(v integer) returns void as $$
                   declare r integer;
                   begin execute 'update foox set a = ' || v || '
where a = ' || v;
                      get diagnostics r = row_count;
                      raise notice '%', r;
                  end; $$ language plpgsql;
CREATE FUNCTION
postgres=# select gg(11);
NOTICE:  0
 gg
----

(1 row)

postgres=# select gg(10);
NOTICE:  1
 gg
----


Regards
Pavel Stehule




>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

In response to

Responses

pgsql-general by date

Next:From: hubert depesz lubaczewskiDate: 2008-06-27 11:16:22
Subject: freebsd + postgresql 8.3.3 = 100% of cpu usage on stats collector?
Previous:From: A BDate: 2008-06-27 08:07:40
Subject: Re: Problem with FOUND

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group