Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If I execute the following in a file with the pgsql command, it complains that the procedure needs to be called with 'perform'. But when I try to use perform, it tells me that perform is not defined. What's the solution?

create or replace function waitris() returns void as 
$$
declare
    cnt integer;
begin
    loop
        select count(*) into cnt from taskflag where taskid = 'rdfdb' and state != 2;
        if cnt = 0 then   
            exit;
        end if;
        select pg_sleep(1);
    end loop;
end;
$$ 
language plpgsql;

select waitris();
share|improve this question

1 Answer 1

up vote 4 down vote accepted

There is no problem with how you call your function. The error you get comes from executing it if and only if cnt is greater than zero. The error you get is

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "waitris" line 9 at SQL statement

and it clearly states that the problem lies in the SELECT pg_sleep(1); line. That is the place you should replace SELECT with PERFORM.

Otherwise your loop is futile without a CONTINUE. And even if you add that, behaviour of the loop is hard to follow. You'd better use a WHILE loop instead. And still one more: as you see, I changed the variable name count to cnt for better readability.

share|improve this answer
    
Thanks. The psql command on my mac did not give me the accurate line number that you got. –  bmargulies May 29 '12 at 19:49
    
@bmargulies: Line numbers for plpgsql error messages always count from the first line of the function body. –  Erwin Brandstetter May 30 '12 at 2:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.