plpgsql variable trouble
От | Phil Steinke |
---|---|
Тема | plpgsql variable trouble |
Дата | |
Msg-id | 20001129094521.B2019@engsoc.queensu.ca обсуждение исходный текст |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: