How to successfully create a new function?

From: Jim Moon <moonjamesg(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: How to successfully create a new function?
Date: 2012-04-03 14:46:53
Message-ID: CAMW+QwHGT1bV0Ox+Lvt3yDgQTKJ-pPsABeXWq3jiF8XdsbAA1A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Jim

--
Jim Moon
503-995-4169

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Jim Moon 2012-04-03 14:53:13 How to connect to remote instance?
Previous Message Bilal Sadiq 2012-03-31 13:36:48 Dynamic Loading Library! Extension using C Function