Postgres Pro
Downloads
Home   >   mailing lists

Thread: void stored procedure does return something?

void stored procedure does return something?

From
Yan Cheng Cheok
Date:
I have the following stored procedure return void.

CREATE OR REPLACE FUNCTION sandbox()
  RETURNS void AS
$BODY$DECLARE
    DECLARE me text;
    DECLARE he int;
BEGIN
    he = 100;
    RAISE NOTICE 'he is %', he;
    -- me = "Hello PostgreSQL";
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION sandbox() OWNER TO postgres;

When I perform query :

SELECT * FROM sandbox();

Everything is fine.

"he is 100" is being printed in message area.

However, when I remove "--" from
me = "Hello PostgreSQL";

I get the following error :
================================================
ERROR:  column "Hello PostgreSQL" does not exist
LINE 1: SELECT  "Hello PostgreSQL"
                ^
QUERY:  SELECT  "Hello PostgreSQL"
CONTEXT:  PL/pgSQL function "sandbox" line 7 at assignment
================================================

But isn't my stored procedure is void? Isn't it shouldn't return anything?

Thanks and Regards
Yan Cheng CHEOK

Re: void stored procedure does return something?

From
Adrian Klaver
Date:
On Thursday 21 January 2010 5:57:14 pm Yan Cheng Cheok wrote:
> I have the following stored procedure return void.
>
> CREATE OR REPLACE FUNCTION sandbox()
>   RETURNS void AS
> $BODY$DECLARE
>     DECLARE me text;
>     DECLARE he int;
> BEGIN
>     he = 100;
>     RAISE NOTICE 'he is %', he;
>     -- me = "Hello PostgreSQL";
> END;$BODY$
>   LANGUAGE 'plpgsql' VOLATILE
>   COST 100;
> ALTER FUNCTION sandbox() OWNER TO postgres;
>
> When I perform query :
>
> SELECT * FROM sandbox();
>
> Everything is fine.
>
> "he is 100" is being printed in message area.
>
> However, when I remove "--" from
> me = "Hello PostgreSQL";
>
> I get the following error :
> ================================================
> ERROR:  column "Hello PostgreSQL" does not exist
> LINE 1: SELECT  "Hello PostgreSQL"
>                 ^
> QUERY:  SELECT  "Hello PostgreSQL"
> CONTEXT:  PL/pgSQL function "sandbox" line 7 at assignment
> ================================================
>
> But isn't my stored procedure is void? Isn't it shouldn't return anything?
>
> Thanks and Regards
> Yan Cheng CHEOK


You need to single quote the string like this; 'Hello PostgreSQL'
Double quotes are for identifiers.
See here for full explanation.
http://www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html

--
Adrian Klaver
adrian.klaver@gmail.com