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