Обсуждение: 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 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
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