Thread: Anybody see the post?

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

Weiping He <laser@zhengmai.com.cn> writes:
>      it seems PERFORM won't affect result status while doing
>      SELECT ... query, but EXECUTE do.

It works for me.

>          raise notice '' SQL: % '', qry;
> --      execute qry;
>          perform qry;

This should give you an error.  perform doesn't take a string
expression.  You want something more like

    perform t FROM test where ...

            regards, tom lane

Tom Lane wrote:
>
> Weiping He <laser@zhengmai.com.cn> writes:
> >      it seems PERFORM won't affect result status while doing
> >      SELECT ... query, but EXECUTE do.
>
> It works for me.
>
> >          raise notice '' SQL: % '', qry;
> > --      execute qry;
> >          perform qry;
>
> This should give you an error.  perform doesn't take a string
> expression.  You want something more like
>
>         perform t FROM test where ...

PERFORM was originally added because we have no resultless procedures,
only functions, and calling a function with SELECT and discarding the
result seemed unobvious, so PERFORM does exactly that for you. It's
useless syntactic sugar.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #