To: the inquirer <listquestions@yahoo.com>
Cc: pgsql-general@postgresql.org, pgsql-novice@postgresql.org,
	pgsql-sql@postgresql.org
Subject: Re: PL/pgSQL Function Problem
References: <20040909174926.81383.qmail@web53002.mail.yahoo.com>
From: Doug McNaught <doug@mcnaught.org>
Date: Sat, 11 Sep 2004 09:55:42 -0400
In-Reply-To: <20040909174926.81383.qmail@web53002.mail.yahoo.com> (the
	inquirer's message of "Thu, 9 Sep 2004 10:49:26 -0700 (PDT)")
Message-ID: <87pt4sap0h.fsf@asmodeus.mcnaught.org>
User-Agent: Gnus/5.1002 (Gnus v5.10.2) Emacs/20.7 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

the inquirer <listquestions@yahoo.com> writes:

> ERROR: syntax error at or near "$1" at character 14
> CONTEXT: PL/pgSQL function "create_author" line 7 at
> SQL statement

PL/pgSQL errors are sometimes obscure.  I'll try to comment on what
looks wrong to me...

> CREATE OR REPLACE FUNCTION create_author (
> VARCHAR(32), VARCHAR(32), VARCHAR(32) ) 
> RETURNS INTEGER AS '
> 	DECLARE
> 		name_		ALIAS FOR $1;
> 		username_	ALIAS FOR $2;
> 		password_	ALIAS FOR $3;
> 		authorid_	INTEGER;
> 	BEGIN
> 		CREATE USER username_ WITH ENCRYPTED PASSWORD
> password_ IN GROUP authors;

I can't tell you why, but my suspicion is that you need to use EXECUTE
in order to do CREATE USER in a function.  Try that. 

> 		INSERT INTO Authors 

Be aware that, unless you double-quote the identifier, "Author" will
be folded to "author" by the SQL parser.  This may also be causing a
problem--you didn't post your schema.

> 			( Name, Username ) 
> 		VALUES 
> 			( $1, $2 );
> 		SELECT Max( AuthorID ) INTO authorid_ FROM Authors;

This is a potential race, depending on your transaction isolation
level.  It would be better to find out the sequence name for your
SERIAL column and user currval('<that sequence>') as the return value.

Hope this helps!

-Doug
-- 
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863