Thread: BUG #6043: Compilation PLpgsql Succesful but execution bad
The following bug has been logged online: Bug reference: 6043 Logged by: Emanuel Email address: postgres.arg@gmail.com PostgreSQL version: 9.1 beta Operating system: Ubuntu 10.04 2.6.31 Description: Compilation PLpgsql Succesful but execution bad Details: 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. Thougths?
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
El 27/05/2011 16:18, "Heikki Linnakangas" < heikki.linnakangas@enterprisedb.com> escribi=C3=B3: > On 27.05.2011 17:05, Emanuel wrote: >> postgres=3D# 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=3D# 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. Thanks Heikki for your fast response! ^^ > 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. > Yeah, it's not a very big concern, althougth cold be taken for future improvements in plpgsql. I very far for submit a patch :P Regards, E
2011/5/28 Emanuel Calvo <postgres.arg@gmail.com>: > El 27/05/2011 16:18, "Heikki Linnakangas" > <heikki.linnakangas@enterprisedb.com> escribi=C3=B3: >> On 27.05.2011 17:05, Emanuel wrote: >>> postgres=3D# 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=3D# 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. > > Thanks Heikki for your fast response! ^^ > > >> 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. >> > > Yeah, it's not a very big concern, althougth cold be taken for future > improvements > in plpgsql. I very far for submit a patch :P > The deep check of embedded SQL is not possible in PL/pgSQL - this remove dependency between PL/pgSQL and database objects. Deeper checks mean a broken compatibility :(. PL/PSM has different philosophy where full check is implemented now. Regards Pavel Stehule > Regards, > E >
>> >> Thanks Heikki for your fast response! ^^ >> >> >>> 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. >>> >> >> Yeah, it's not a very big concern, althougth cold be taken for future >> improvements >> in plpgsql. I very far for submit a patch :P >> > > The deep check of embedded SQL is not possible in PL/pgSQL - =C2=A0this > remove dependency between PL/pgSQL and database objects. Deeper checks > mean a broken compatibility :(. > Good point. > PL/PSM has different philosophy where full check is implemented now. > Do you think that make some test in 9.1 worthwhile for this language? I see that the last contrib was submitted years ago. Regards, --=20 -- =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Emanuel Calvo =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Helpame.com
2011/5/28 Emanuel Calvo <postgres.arg@gmail.com>: >>> >>> Thanks Heikki for your fast response! ^^ >>> >>> >>>> 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. >>>> >>> >>> Yeah, it's not a very big concern, althougth cold be taken for future >>> improvements >>> in plpgsql. I very far for submit a patch :P >>> >> >> The deep check of embedded SQL is not possible in PL/pgSQL - =C2=A0this >> remove dependency between PL/pgSQL and database objects. Deeper checks >> mean a broken compatibility :(. >> > > Good point. > >> PL/PSM has different philosophy where full check is implemented now. >> > > Do you think that make some test in 9.1 =C2=A0worthwhile for this > language? I see that the last contrib was submitted years ago. > I worked on new implementation called PL/PSM - but it is not mature. It should to work with 9.1. https://github.com/okbob/plpsm0 all features https://github.com/okbob/plpsm0/blob/master/test.sql are supported, but there are no real project that is based on this language now. Regards Pavel Stehule > Regards, > > > -- > -- > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Emanuel Calvo > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Helpame.com >