PL/pgSQL Function Problem

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