question about PERFORM and EXECUTE in plpgsql
От | Weiping He |
---|---|
Тема | question about PERFORM and EXECUTE in plpgsql |
Дата | |
Msg-id | 3E519E9C.70809@zhengmai.com.cn обсуждение исходный текст |
Список | 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 по дате отправления: