Anybody see the post? - Mailing list pgsql-general

From Weiping He
Subject Anybody see the post?
Date
Msg-id 3E51F26C.5040205@zhengmai.com.cn
Whole thread Raw
Responses Re: Anybody see the post?
List pgsql-general
hi,
      I met a weird while using perform and execute in plpgsql.
     the phenomena:
     it seems PERFORM won't affect result status while doing
     SELECT ... query, but EXECUTE do.I think the docs should
     make it more clear if it's true.
     from the docs for PERFORUM:
     " This executes a SELECT query and discards the result. PL/pgSQL
variables are substituted in the query as usual. Also, the special
variable FOUND is set to true if the query produced at least one row, or
false if it produced no rows."
     I think I should FOUND variable should be false in the example
     below, but, my test shows me the opposite.

     my version:

laser_uni=# select version();
                                   version
---------------------------------------------------------------------------
  PostgreSQL 7.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.1
(1 row)

     here my test code:

---------------------------8<---------------------------------------------

drop table test;
create table test (t text);

CREATE or replace FUNCTION test()
RETURNS text
AS '
DECLARE
         prefix text;
         maxint integer;
         ret text;
         qry text;
         rows integer;

         BEGIN
         prefix := cast(date_part(\'year\', now())::integer - 1911 as
text) || lpad(date_part(\'month\', now()), 2, \'0\');
         qry := ''SELECT t FROM test where t LIKE '' ||
                 quote_literal(prefix) ||
                 ''||'' ||
                 quote_literal(''%'') ||
                 '' limit 1'';
         raise notice '' SQL: % '', qry;
--      execute qry;
         perform qry;
         get diagnostics rows = ROW_COUNT;
         raise notice '' rows: % '', rows;
         IF FOUND
         THEN
         raise notice ''found'';

     ret = ''found'';
         ELSE
         raise notice ''not found'';
         ret = ''not found'';
         END IF;
         RETURN ret;
         END;'
LANGUAGE 'plpgsql';

----------------------------8<---------------------------------------------

regards    laser



pgsql-general by date:

Previous
From: Emmanuel Charpentier
Date:
Subject: Aggregate definition : small oversight ?
Next
From: Steinar Bang
Date:
Subject: Re: How do people do schema upgrades?