From: Doug McNaught <doug@mcnaught.org>
To: the inquirer <listquestions@yahoo.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-novice@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: PL/pgSQL Function Problem
Date: Sat, 11 Sep 2004 09:55:42 -0400
Message-ID: <87pt4sap0h.fsf@asmodeus.mcnaught.org> (raw)
In-Reply-To: <20040909174926.81383.qmail@web53002.mail.yahoo.com>
References: <20040909174926.81383.qmail@web53002.mail.yahoo.com>
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
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: doug@mcnaught.org, listquestions@yahoo.com, pgsql-general@postgresql.org, pgsql-novice@postgresql.org
Subject: Re: PL/pgSQL Function Problem
In-Reply-To: <87pt4sap0h.fsf@asmodeus.mcnaught.org>
* 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