Re: [GENERAL] PL/pgSQL Function Problem

2004-09-11 Thread Tom Lane 
the inquirer [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] PL/pgSQL Function Problem

2004-09-10 Thread the inquirer 
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

---(end of broadcast)---
TIP 8: explain analyze is your friend