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 по дате отправления:

Предыдущее
От: Lamar Owen
Дата:
Сообщение: Re: v7.1 Beta?
Следующее
От: Zachary Beane
Дата:
Сообщение: names in WHERE and HAVING