Thread: plpgsql variable trouble

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

Phil Steinke <lintec@engsoc.queensu.ca> writes:
> The problem with my code seems to be that the "given_field" variable isn't
> being interpolated in the assignment statement.

plpgsql is not a string-substitution language; you cannot expect the
value of a variable to be used as a table or field name in a query.
This is true because plpgsql precompiles queries into query plans
and saves the plans for repeated execution.  That's a win for speed
but costs flexibility.

You *can* get that sort of result in pltcl or plperl, which don't do
any fancy caching.  You just form the query as a string value using the
usual expression evaluation rules of those languages, and that string
gets submitted to the SQL parser and query engine.  Less speed, more
flexibility.

I believe 7.1's plpgsql will have an EXECUTE <string> command that lets
you get the second effect in plpgsql too.

            regards, tom lane