Hi All,
The original reason I tied FUNCTIONS is that I need to extend ROLE
definition as stored within pg_authid system table, with some (more or
less arbitrary) user preferencies profile.
At this point, the task comes down to the point where I can imagine
having an additional table PEOPLE(rolename, privID, etc,...), which
keeps rolenames from pg_authid togather with my additional profile data,
and is extended/truncated by special functions, which also create/drop
roles accordingly.
Now, after a brief lecture of 'Chapter 32. Extending SQL' I cannot
figure out the way to create such functions.
Ultimately, I think I'll use 'LANGUAGE C', but for the sake of stating
the problem I'll use 'LANGUAGE SQL' here. So I have:
CREATE TABLE people (username text not null, -- pg_authid.rolname
first_name text, last_name text, age int);
CREATE FUNCTION new_user (text,text) RETURNS SETOF people AS $$
CREATE ROLE $1 PASSWORD $2;
SELECT CASE WHEN $2 is not null THEN ROW($1, null, null, null)::people
ELSE null::ludzie END
$$ language sql;
CREATE VIEW my_people AS SELLECT * from people;
CREATE RULE more_people AS ON INSERT TO my_people WHERE new.username IS
NOT NULL DO INSTEAD INSERT INTO people
(username,first_name,last_name,age) VALUES (new_user(new.username,
null));
In other words, with the above plan I'd like to insert a new ROW into
PEOPLE table, when my function NEW_USER() *returns* a valid user record.
Otherwise (e.g. when NEW_USER(), for one reason or another, fails to
create a new ROLE), it should not return anything, and thus make a NULL
insert, thusly NOT create a user profile for none-existant user.
The reason I use "returns SETOF" here is that I plan to have it return
ZERO or ONE row for people table. (without the SETOF, something is
ALWAYS returned).
Ideally I wouldn't like to see PostgreSQL complaining about anything -
I'd like to take care of all the error conditions within my NEW_USER()
function.
But, I get:
"ERROR: function returning set of rows cannot return null value"
So my impression, that a function returning SETOF *would* behaves like a
"SELECT" on table (which can return no-rows), was actually not true.
After more reading of examples in "Chapter 32." I've noticed, that none
actually show a function returning "0 rows".
So is this really not possible? Or my function should be written in some
other way?
I hope someone can give me a hand here. Thenx,
-R