Re: PL/pgSQL Function Problem

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