Thread: How to successfully create a new function?

How to successfully create a new function?

From

Jim Moon

Date:

03 April 2012, 15:48:14

plpgsql is installed. Thank you all for the pointers.

This query:

CREATE OR REPLACE FUNCTION "sp_SetScenario"(scen character varying) RETURNS void AS
$BODY$DECLARE
    sqlStatement VARCHAR(500);
BEGIN
    SET sqlStatement = 'ALTER USER postgres WITH DEFAULT_SCHEMA = ' + $1;
    EXEC(sqlStatement);
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

returns:

ERROR: syntax error at or near "$1"
LINE 1: SET $1 = 'ALTER USER postgres WITH DEFAULT_SCHEMA = ' + $...
^
QUERY: SET $1 = 'ALTER USER postgres WITH DEFAULT_SCHEMA = ' + $2
CONTEXT: SQL statement in PL/PgSQL function "sp_SetScenario" near line 4

********** Error **********

ERROR: syntax error at or near "$1"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "sp_SetScenario" near line 4

I am coming from SQL Server--a different animal. Thanks in advance for any more assistance.

Jim

Jim Moon

503-995-4169

Re: How to successfully create a new function?

From

Andreas Kretschmer

Date:

03 April 2012, 17:06:26

Jim Moon <moonjamesg@gmail.com> wrote:

> plpgsql is installed.  Thank you all for the pointers.
>
> This query:
>
> CREATE OR REPLACE FUNCTION "sp_SetScenario"(scen character varying) RETURNS
> void AS
> $BODY$DECLARE
>     sqlStatement VARCHAR(500);
> BEGIN
>     SET sqlStatement = 'ALTER USER postgres WITH DEFAULT_SCHEMA = ' + $1;
>     EXEC(sqlStatement);

String-Concatination? Don't use +, use || instead.



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: How to successfully create a new function?

From

Jim Moon

Date:

03 April 2012, 17:28:32

Thank you all again. This works:

CREATE OR REPLACE FUNCTION "sp_SetScenario"(scen character varying) RETURNS void AS $$
DECLARE sqlStatement varchar(500);
BEGIN
sqlStatement = 'ALTER USER postgres WITH DEFAULT_SCHEMA = ' || scen;
EXECUTE(sqlStatement);
END;
$$
LANGUAGE 'plpgsql'

I removed SET from:
      SET sqlStatement = 'ALTER USER postgres ...

I changed:
     EXEC(sqlStatement);
to:
     EXECUTE(sqlStatement);

Jim