Thread: How to successfully create a new function?

______________________________________________________________________
NOTICE: This communication and any attachments ("this message") may contain confidential information for the sole use of the intended recipient(s). Any unauthorized use, disclosure, viewing, copying, alteration, dissemination or distribution of, or reliance on this message is strictly prohibited. If you have received this message in error, or you are not an authorized recipient, please notify the sender immediately by replying to this message, delete this message and all copies from your e-mail system and destroy any printed copies.

Moon, James <moonj@pbworld.com> wrote:

> Good day!  A little help, please….
>
>
>
> Postgresql 8.3
>
> pgAdmin III  v.1.8.4
>
>
>
> Creating a new function, or modifying an existing function to:
>
>
>
> 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 'sql' VOLATILE
>
> COST 100;


You have to change:

- language pgpgsql, not sql, sql don't contains variables
- change exec() to execute


>
> ______________________________________________________________________
> NOTICE: This communication and any attachments ("this message") may contain
> confidential information for the sole use of the intended recipient(s). Any
> unauthorized use, disclosure, viewing, copying, alteration, dissemination or
> distribution of, or reliance on this message is strictly prohibited. If you
> have received this message in error, or you are not an authorized recipient,
> please notify the sender immediately by replying to this message, delete this
> message and all copies from your e-mail system and destroy any printed copies.

Please try to avoid this bullshit...



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°

Andreas,

Thank you for your advice--query and other:

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';

               EXECUTE(sqlStatement);

END;

$BODY$

LANGUAGE 'pgpsql' VOLATILE

COST 100;

 

 

returns:

 

ERROR:  language "pgpsql" does not exist

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

ERROR: language "pgpsql" does not exist

SQL state: 42704

 

The only language options in the New Function “wizard” in pgAdmin III are:

internal

c

sql

I would be grateful for any further suggestions from you or others.

You should "install" the language in your database.

 Fredag 30. mars 2012 17.17.19 skrev Jim Moon :

> ERROR:  language "pgpsql" does not exist

The language is called plpgsql.

regards, Leif
http://code.google.com/p/yggdrasil-genealogy/

Leif Biberg Kristensen <leif@solumslekt.org> wrote:

>  Fredag 30. mars 2012 17.17.19 skrev Jim Moon :
>
> > ERROR:  language "pgpsql" does not exist
>
> The language is called plpgsql.

Yeah, that was my fault, sorry.


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°