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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match