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

Re: [SQL] PL/pgSQL Function Problem

From: Michalis Kabrianis <mk(at)interzone(dot)gr>
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: [SQL] PL/pgSQL Function Problem
Date: 2004-09-11 06:22:36
Message-ID: 414299AC.5040900@interzone.gr (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novicepgsql-sql

the inquirer wrote:
> 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.  Any help would be
> greatly appreciated.
> 
> SELECT create_author( 'name', 'username', 'password'
> );
> 
> ERROR: syntax error at or near "$1" at character 14
> CONTEXT: PL/pgSQL function "create_author" line 7 at
> SQL statement
> 
> Here is the code:
> 
> 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;
> 
> 		INSERT INTO Authors 
> 			( Name, Username ) 
> 		VALUES 
> 			( $1, $2 );
> 		SELECT Max( AuthorID ) INTO authorid_ FROM Authors;
> 
> 		RETURN authorid_;
> 	
> 	END;
> ' LANGUAGE 'plpgsql'
> SECURITY INVOKER
> RETURNS NULL ON NULL INPUT;
> 
> 
> 		

As Tom Lane said before me, use EXECUTE.

I have that on a similar project

CREATE FUNCTION s_user() RETURNS "trigger"
     AS '
DECLARE
    uname text;
BEGIN
    uname := ''s'' || NEW.code::character varying;
    EXECUTE ''CREATE USER ''||uname||'' WITH ENCRYPTED PASSWORD 
''''pass'''' NOCREATEDB NOCREATEUSER IN GROUP salesmen;'';
    RETURN NEW;
END
'
     LANGUAGE plpgsql SECURITY DEFINER;

It's obviously is a trigger on an insert on some table, and creates the 
username based on that tables' primary key. It also sets a standard 
password, to be canged by the user.

I use it with SECURITY DEFINER because users that use that piece of code 
are ordinary users and don't have the right to create users in any other 
way.

Michalis

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2004-09-11 06:22:47
Subject: Re: forcing date ordering
Previous:From: Tom LaneDate: 2004-09-11 06:19:16
Subject: Re: Alter session set current_schema

pgsql-general by date

Next:From: Greg StarkDate: 2004-09-11 06:30:35
Subject: Re: Speeding up LIKE with placeholders?
Previous:From: Tom LaneDate: 2004-09-11 06:10:47
Subject: Re: stringToNode() for plan nodes...

pgsql-sql by date

Next:From: Tom LaneDate: 2004-09-11 06:42:23
Subject: Re: CREATE RULE ignored, what did I do wrong
Previous:From: Tom LaneDate: 2004-09-11 06:17:03
Subject: Re: Using UPDATE FROM

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