How to successfully create a new function?
| От | Jim Moon |
|---|---|
| Тема | How to successfully create a new function? |
| Дата | |
| Msg-id | CAMW+QwHGT1bV0Ox+Lvt3yDgQTKJ-pPsABeXWq3jiF8XdsbAA1A@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: How to successfully create a new function?
|
| Список | pgsql-novice |
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.
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
В списке pgsql-novice по дате отправления: