the inquirer <listquestions(at)yahoo(dot)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 '
> name_ ALIAS FOR $1;
> username_ ALIAS FOR $2;
> password_ ALIAS FOR $3;
> authorid_ INTEGER;
> 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 )
> ( $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!
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
In response to
pgsql-novice by date
|Next:||From: Ennio-Sr||Date: 2004-09-11 14:00:43|
|Subject: Re: iso-8859-2|
|Previous:||From: Tom Lane||Date: 2004-09-11 06:22:47|
|Subject: Re: forcing date ordering |
pgsql-general by date
|Next:||From: William Herring||Date: 2004-09-11 14:04:54|
|Subject: table with a variable name???|
|Previous:||From: Doug McNaught||Date: 2004-09-11 13:43:39|
|Subject: Re: One Database per Data File?|
pgsql-sql by date
|Next:||From: m0ntar3||Date: 2004-09-11 20:42:46|
|Subject: create unique index schema.index_name on table (column)?|
|Previous:||From: Michael Glaesemann||Date: 2004-09-11 12:57:23|
|Subject: Re: HOW TO HANDLE ZEROS IN DATE FIELD?|