Re: void stored procedure does return something?

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(dot)klaver(at)gmail(dot)com