Thread: 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 <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
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