PL/pgSQL Function Problem

[the inquirer]

I am trying to create a function that creates a user
and adds a row to a table. It produces no warnings or
errors when I create the function but when I attempt
to execute it I get a syntax error. I do not
understand why this is happening. Any help would be
greatly appreciated.

SELECT create_author( 'name', 'username', 'password'
);

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

Here is the code:

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;

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

RETURN authorid_;

END;
' LANGUAGE 'plpgsql'
SECURITY INVOKER
RETURNS NULL ON NULL INPUT;

_______________ _______________ ____
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Doug McNaught]

the inquirer <li***********@ yahoo.com> writes:

ERROR: syntax error at or near "$1" at character 14
CONTEXT: PL/pgSQL function "create_aut hor" 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)