Message-ID: <414299AC.5040900@interzone.gr>
Date: Sat, 11 Sep 2004 09:22:36 +0300
From: Michalis Kabrianis <mk@interzone.gr>
Organization: Interzone
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7) Gecko/20040616
MIME-Version: 1.0
To: the inquirer <listquestions@yahoo.com>
Cc: pgsql-general@postgresql.org, pgsql-novice@postgresql.org,
	pgsql-sql@postgresql.org
Subject: Re: [SQL] PL/pgSQL Function Problem
References: <20040909174926.81383.qmail@web53002.mail.yahoo.com>
In-Reply-To: <20040909174926.81383.qmail@web53002.mail.yahoo.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit


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