To: the inquirer <listquestions@yahoo.com>
Cc: pgsql-general@postgresql.org, pgsql-novice@postgresql.org,
	pgsql-sql@postgresql.org
Subject: Re: PL/pgSQL Function Problem 
In-reply-to: <20040909174926.81383.qmail@web53002.mail.yahoo.com> 
References: <20040909174926.81383.qmail@web53002.mail.yahoo.com>
Comments: In-reply-to the inquirer <listquestions@yahoo.com>
	message dated "Thu, 09 Sep 2004 10:49:26 -0700"
Date: Sat, 11 Sep 2004 02:03:30 -0400
Message-ID: <7435.1094882610@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

the inquirer <listquestions@yahoo.com> writes:
> 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.

> 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;

Utility statements (which is to say anything except SELECT/INSERT/
UPDATE/DELETE) generally don't cope with parameters.  The above won't
work because it's trying to substitute parameters for username_ and
password_ in the CREATE USER utility statement.  You could make it work
by constructing the CREATE USER command as a string and then EXECUTE'ing
it.

(I agree this ain't ideal, but it's where we're at...)

			regards, tom lane