Re: BUG #6043: Compilation PLpgsql Succesful but execution bad

On 27.05.2011 17:05, Emanuel wrote:
> postgres=# CREATE OR REPLACE FUNCTION p_() RETURNS TABLE (i int) AS $$
> DECLARE
> BEGIN
> SELECT * FROM p; --<<<-- here must ne RETURN QUERY ..
> END;
> $$ LANGUAGE plpgsql;
> CREATE FUNCTION
> postgres=# select p_();
> 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 "p_" line 4 at SQL statement
>
> I don't know if it's really a bug or a feature request. But seems that the
> function compiles well without checking the existence of a RETURN QUERY. I
> think the best in this cases is raise an error during compilation.

Yeah, the PL/pgSQL compiler isn't smart enough to catch that at
compilation time. It's easy to see that there's a RETURN missing from a
simple function like that, but not so easy in general. For example:

CREATE FUNCTION AS foo() RETURNS text $$
declare
i int4;
begin
i := 0;
loop
IF i = 100 THEN
RETURN 'done';
END IF
end
end;
$$ LANGUAGE plpgsql;

The compiler would have to determine that the loop never ends, or it
would complain that there's no RETURN at the end.

Many compilers for other languages do that kind of analysis, but it
usually only results in a warning, and compilers sometimes get that
wrong. I don't think it's worthwhile to do that, but of course, patches
are welcome.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com