Re: [SQL] PL/pgSQL Function Problem

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