Skip site navigation (1) Skip section navigation (2)

Re: PL/pgSQL Function Problem

From: Doug McNaught <doug(at)mcnaught(dot)org>
To: the inquirer <listquestions(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org,pgsql-sql(at)postgresql(dot)org
Subject: Re: PL/pgSQL Function Problem
Date: 2004-09-11 13:55:42
Message-ID: 87pt4sap0h.fsf@asmodeus.mcnaught.org (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novicepgsql-sql
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 '
> 	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

In response to

pgsql-novice by date

Next:From: Ennio-SrDate: 2004-09-11 14:00:43
Subject: Re: iso-8859-2
Previous:From: Tom LaneDate: 2004-09-11 06:22:47
Subject: Re: forcing date ordering

pgsql-general by date

Next:From: William HerringDate: 2004-09-11 14:04:54
Subject: table with a variable name???
Previous:From: Doug McNaughtDate: 2004-09-11 13:43:39
Subject: Re: One Database per Data File?

pgsql-sql by date

Next:From: m0ntar3Date: 2004-09-11 20:42:46
Subject: create unique index schema.index_name on table (column)?
Previous:From: Michael GlaesemannDate: 2004-09-11 12:57:23
Subject: Re: HOW TO HANDLE ZEROS IN DATE FIELD?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group