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
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
