Re: Postgres Function in plpgsql, returning null results to Postfix

Thanks to all those who replied.

I can get things to work if the plpgsql function returns "SETOF", rather than
write a function that returns one record (thanks Steve).

i.e. This sort of function doesn't work (unless I'm missing something Javier
has a function like this).

CREATE OR REPLACE FUNCTION virtual_user(VARCHAR) RETURNS VARCHAR as '
DECLARE
fname ALIAS FOR $1;
result RECORD;
BEGIN
SELECT INTO result touser FROM email_virtual WHERE fromuser = fname ;
IF NOT FOUND THEN
result.touser:=NULL;
END IF;
RETURN result.touser ;
END;
' LANGUAGE plpgsql;

This one does.

CREATE OR REPLACE FUNCTION virtual_users(VARCHAR) RETURNS SETOF VARCHAR as '
DECLARE

-- Intended for Postfix virtual_alias_maps

fname ALIAS FOR $1;
result RECORD;

BEGIN
FOR result IN SELECT touser FROM email_virtual WHERE fromuser = fname LOOP
RETURN NEXT result.touser ;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;

The later needs to be called as "query = select * from virtual_users('%s');"
in the Postfix "cf" file.

Otherwise you get errors like;

postmap: warning: table pgsql:/etc/postfix/pgsql-email-virtual.cf: key
simow@zyworld.com: empty string result is not allowed
postmap: warning: table pgsql:/etc/postfix/pgsql-email-virtual.cf should
return NO RESULT in case of NOT FOUND

However I have seen suggestion that Postfix can call SQL queries with the
syntax "query = select functionname('%s');" so I assume there is a way to
persuade Postfix to accept that data is missing from the first type of
function. I just haven't understood it. Or perhaps the later format only
applies to things like access tables where a returned value is always present
"OK|REJECT|..." for every key. I looked at the source and bemoaned the
appalling state of my C know-how again.

For my own use I have reverted to just putting the SQL queries in the Postfix
config files because they are simple queries.

But using functions seems much more elegant way of hiding the database
structure from Postfix, and I need a function for the check_recipient_access
map at work, to implement the suggestion by mouss on validating virtual email
addresses before queuing to disk, because there we have 25000+ addresses, and
a thousand+ domains, and for my own use I currently have one domain, and just
a handful of virtual addresses.

(And this is all Postfix 2.1, and yes I know Postfix 2.2 changes a lot of
this, but so far everything else has been done with Debian Sarge standard
packages, and it will make management a lot easier if I can stick with Sarge
packages everywhere).