Обсуждение: Function's final statement must be a SELECT
I am trying to write a function in PgAdmin 1.8.2 for postgres 8.3. The function should check the input parameter for NULL. If its NULL then function should return 'Auto' otherwise return the input as such.
CREATE FUNCTION check_for_null( inp character varying) RETURNS character varying AS $$
DECLARE
temp1 character varying := 'Auto';
temp2 character varying := inp;
BEGIN
IF temp2 IS NOT NULL THEN
temp1 := temp2;
END IF;
RETURN temp1;
END;
$$ LANGUAGE SQL;
When I hit 'OK' in create function window, I get following error :-
ERROR: return type mismatch in function declared to return character varying
DETAIL: Function's final statement must be a SELECT.
CONTEXT: SQL function "check_for_null"
Any suggestions why this happens ??
Thanks
On Wed, May 14, 2008 at 9:23 PM, Premil Agarwal <preagarw@gmail.com> wrote: > I am trying to write a function in PgAdmin 1.8.2 for postgres 8.3. The > function should check the input parameter for NULL. If its NULL then > function should return 'Auto' otherwise return the input as such. > > CREATE FUNCTION check_for_null( inp character varying) RETURNS character > varying AS $$ > DECLARE > temp1 character varying := 'Auto'; > temp2 character varying := inp; > BEGIN > > IF temp2 IS NOT NULL THEN > temp1 := temp2; > END IF; > > RETURN temp1; > END; > $$ LANGUAGE SQL; > > When I hit 'OK' in create function window, I get following error :- > > ERROR: return type mismatch in function declared to return character varying > DETAIL: Function's final statement must be a SELECT. > CONTEXT: SQL function "check_for_null" > > Any suggestions why this happens ?? Choose pl/pgsql as the language, not SQL. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Premil Agarwal escribió: > I am trying to write a function in PgAdmin 1.8.2 for postgres 8.3. The > function should check the input parameter for NULL. If its NULL then > function should return 'Auto' otherwise return the input as such. > > CREATE FUNCTION check_for_null( inp character varying) RETURNS character > varying AS $$ [...] > $$ LANGUAGE SQL; ^^^ There's your problem. Try using PLPGSQL instead. In any case, you can do this with COALESCE() and it's much easier. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Choose pl/pgsql as the language, not SQL.On Wed, May 14, 2008 at 9:23 PM, Premil Agarwal <preagarw@gmail.com> wrote:
> I am trying to write a function in PgAdmin 1.8.2 for postgres 8.3. The
> function should check the input parameter for NULL. If its NULL then
> function should return 'Auto' otherwise return the input as such.
>
> CREATE FUNCTION check_for_null( inp character varying) RETURNS character
> varying AS $$
> DECLARE
> temp1 character varying := 'Auto';
> temp2 character varying := inp;
> BEGIN
>
> IF temp2 IS NOT NULL THEN
> temp1 := temp2;
> END IF;
>
> RETURN temp1;
> END;
> $$ LANGUAGE SQL;
>
> When I hit 'OK' in create function window, I get following error :-
>
> ERROR: return type mismatch in function declared to return character varying
> DETAIL: Function's final statement must be a SELECT.
> CONTEXT: SQL function "check_for_null"
>
> Any suggestions why this happens ??
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
( BTW, replacing "SQL" by "PLPGSQL " still gets the same error ..... )
Thanks a lot Alvaro !!!
Premil Agarwal escribió:> I am trying to write a function in PgAdmin 1.8.2 for postgres 8.3. The[...]
> function should check the input parameter for NULL. If its NULL then
> function should return 'Auto' otherwise return the input as such.
>
> CREATE FUNCTION check_for_null( inp character varying) RETURNS character
> varying AS $$
> $$ LANGUAGE SQL;
^^^
There's your problem. Try using PLPGSQL instead.
In any case, you can do this with COALESCE() and it's much easier.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
On Wed, May 14, 2008 at 9:38 PM, Premil Agarwal <preagarw@gmail.com> wrote: > Tried that already, still the same error It (using plpgsql instead of sql) works fine for me using your code. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com