Hi, I'm trying to write my first simple function in plpgsql and am having a
bit of trouble. First, the code:
CREATE FUNCTION can_publish(text, text) RETURNS text AS '
DECLARE
given_handle ALIAS FOR $1;
given_field text;
result text;
BEGIN
given_field := $2;
IF (given_field ~ ''^address'') THEN
given_field = ''address'';
END IF;
result := "given_field" FROM publish WHERE handle = "given_handle";
IF NOT FOUND THEN
RAISE EXCEPTION ''publish field not found. result is %'', result;
-- RETURN true;
END IF;
RETURN result;
END;
' LANGUAGE 'plpgsql';
The idea is I have two similar tables. One contains data, and the other
says whether or not the user would like each item of data published.
However, there are some fields with which they have no choice; these are
always published, and aren't in the publish table.
What I want is a function that given a unique handle (username) and field
name, will tell me if I should publish that datum for that user. If the
field doesn't exist in publish, it should go ahead. Otherwise, it should
use the value from the publish table.
The problem with my code seems to be that the "given_field" variable isn't
being interpolated in the assignment statement. No matter whether I try a
valid or invalid field, it always returns something like
phpregistry=> SELECT can_publish('lintec', 'email_personal');
ERROR: publish field not found. result is email_personal
Any help would be greatly appreciated.
Phil
