pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction's final statement must be a SELECT
6+ messages / 3 participants
[nested] [flat]
* Function's final statement must be a SELECT
@ 2008-05-14 20:23 Premil Agarwal <preagarw@gmail.com>
2008-05-14 20:36 ` Re: [INTERFACES] Function's final statement must be a SELECT Dave Page <dpage@pgadmin.org>
2008-05-14 20:36 ` Re: Function's final statement must be a SELECT Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 6+ messages in thread
From: Premil Agarwal @ 2008-05-14 20:23 UTC (permalink / raw)
To: pgsql-interfaces; pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] Function's final statement must be a SELECT
2008-05-14 20:23 Function's final statement must be a SELECT Premil Agarwal <preagarw@gmail.com>
@ 2008-05-14 20:36 ` Dave Page <dpage@pgadmin.org>
2008-05-14 20:38 ` Re: [INTERFACES] Function's final statement must be a SELECT Premil Agarwal <preagarw@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Dave Page @ 2008-05-14 20:36 UTC (permalink / raw)
To: Premil Agarwal <preagarw@gmail.com>; +Cc: pgsql-interfaces; pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] Function's final statement must be a SELECT
2008-05-14 20:23 Function's final statement must be a SELECT Premil Agarwal <preagarw@gmail.com>
2008-05-14 20:36 ` Re: [INTERFACES] Function's final statement must be a SELECT Dave Page <dpage@pgadmin.org>
@ 2008-05-14 20:38 ` Premil Agarwal <preagarw@gmail.com>
2008-05-14 20:49 ` Re: [INTERFACES] Function's final statement must be a SELECT Dave Page <dpage@pgadmin.org>
0 siblings, 1 reply; 6+ messages in thread
From: Premil Agarwal @ 2008-05-14 20:38 UTC (permalink / raw)
To: Dave Page <dpage@pgadmin.org>; +Cc: pgsql-interfaces; pgsql-admin@postgresql.org
Tried that already, still the same error
On Wed, May 14, 2008 at 4:36 PM, Dave Page <dpage@pgadmin.org> wrote:
> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] Function's final statement must be a SELECT
2008-05-14 20:23 Function's final statement must be a SELECT Premil Agarwal <preagarw@gmail.com>
2008-05-14 20:36 ` Re: [INTERFACES] Function's final statement must be a SELECT Dave Page <dpage@pgadmin.org>
2008-05-14 20:38 ` Re: [INTERFACES] Function's final statement must be a SELECT Premil Agarwal <preagarw@gmail.com>
@ 2008-05-14 20:49 ` Dave Page <dpage@pgadmin.org>
0 siblings, 0 replies; 6+ messages in thread
From: Dave Page @ 2008-05-14 20:49 UTC (permalink / raw)
To: Premil Agarwal <preagarw@gmail.com>; +Cc: pgsql-interfaces; pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Function's final statement must be a SELECT
2008-05-14 20:23 Function's final statement must be a SELECT Premil Agarwal <preagarw@gmail.com>
@ 2008-05-14 20:36 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-05-14 20:47 ` Re: Function's final statement must be a SELECT Premil Agarwal <preagarw@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2008-05-14 20:36 UTC (permalink / raw)
To: Premil Agarwal <preagarw@gmail.com>; +Cc: pgsql-interfaces; pgsql-admin@postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Function's final statement must be a SELECT
2008-05-14 20:23 Function's final statement must be a SELECT Premil Agarwal <preagarw@gmail.com>
2008-05-14 20:36 ` Re: Function's final statement must be a SELECT Alvaro Herrera <alvherre@commandprompt.com>
@ 2008-05-14 20:47 ` Premil Agarwal <preagarw@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Premil Agarwal @ 2008-05-14 20:47 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-interfaces; pgsql-admin@postgresql.org
Great !!! COALESCE() does the job for me.
( BTW, replacing "SQL" by "PLPGSQL " still gets the same error ..... )
Thanks a lot Alvaro !!!
On Wed, May 14, 2008 at 4:36 PM, Alvaro Herrera <alvherre@commandprompt.com>
wrote:
> 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.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-05-14 20:49 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-14 20:23 Function's final statement must be a SELECT Premil Agarwal <preagarw@gmail.com>
2008-05-14 20:36 ` Dave Page <dpage@pgadmin.org>
2008-05-14 20:38 ` Premil Agarwal <preagarw@gmail.com>
2008-05-14 20:49 ` Dave Page <dpage@pgadmin.org>
2008-05-14 20:36 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-05-14 20:47 ` Premil Agarwal <preagarw@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox