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

Re: PL/pgSQL Function Problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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 06:03:30
Message-ID: 7435.1094882610@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novicepgsql-sql
the inquirer <listquestions(at)yahoo(dot)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

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2004-09-11 06:19:16
Subject: Re: Alter session set current_schema
Previous:From: Oliver ElphickDate: 2004-09-11 06:00:11
Subject: Re: Alter session set current_schema

pgsql-general by date

Next:From: Tom LaneDate: 2004-09-11 06:10:47
Subject: Re: stringToNode() for plan nodes...
Previous:From: Martijn van OosterhoutDate: 2004-09-11 05:16:30
Subject: Re: One Database per Data File?

pgsql-sql by date

Next:From: Tom LaneDate: 2004-09-11 06:17:03
Subject: Re: Using UPDATE FROM
Previous:From: Michael GlaesemannDate: 2004-09-11 05:44:02
Subject: Re: HOW TO HANDLE ZEROS IN DATE FIELD?

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