Re: PL/pgSQL Function Problem

the inquirer <listquestions(at)yahoo(dot)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