From: Michalis Kabrianis <mk@interzone.gr>
To: the inquirer <listquestions@yahoo.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-novice@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] PL/pgSQL Function Problem
Date: Sat, 11 Sep 2004 09:22:36 +0300
Message-ID: <414299AC.5040900@interzone.gr> (raw)
In-Reply-To: <20040909174926.81383.qmail@web53002.mail.yahoo.com>
References: <20040909174926.81383.qmail@web53002.mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mk@interzone.gr, listquestions@yahoo.com, pgsql-general@postgresql.org, pgsql-novice@postgresql.org
Subject: Re: [SQL] PL/pgSQL Function Problem
In-Reply-To: <414299AC.5040900@interzone.gr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox