Re: [SQL] PL/pgSQL Function Problem

#1

the inquirer wrote:

Quote:

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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [SQL] PL/pgSQL Function Problem

comp.databases.postgresql.novice comp.databases.postgresql.novice

Re: [SQL] PL/pgSQL Function Problem - 09-11-2004 , 02:22 AM