Обсуждение: PL/pgSQL Function Problem

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_author" 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

the inquirer <listquestions@yahoo.com> writes:
> 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.

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

Utility statements (which is to say anything except SELECT/INSERT/
UPDATE/DELETE) generally don't cope with parameters.  The above won't
work because it's trying to substitute parameters for username_ and
password_ in the CREATE USER utility statement.  You could make it work
by constructing the CREATE USER command as a string and then EXECUTE'ing
it.

(I agree this ain't ideal, but it's where we're at...)

            regards, tom lane

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

the inquirer wrote:
> 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_author" 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;
>
>
>

As Tom Lane said before me, use EXECUTE.

I have that on a similar project

CREATE FUNCTION s_user() RETURNS "trigger"
     AS '
DECLARE
    uname text;
BEGIN
    uname := ''s'' || NEW.code::character varying;
    EXECUTE ''CREATE USER ''||uname||'' WITH ENCRYPTED PASSWORD
''''pass'''' NOCREATEDB NOCREATEUSER IN GROUP salesmen;'';
    RETURN NEW;
END
'
     LANGUAGE plpgsql SECURITY DEFINER;

It's obviously is a trigger on an insert on some table, and creates the
username based on that tables' primary key. It also sets a standard
password, to be canged by the user.

I use it with SECURITY DEFINER because users that use that piece of code
are ordinary users and don't have the right to create users in any other
way.

Michalis