plpgsql variable trouble

From: Phil Steinke <lintec(at)engsoc(dot)queensu(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: plpgsql variable trouble
Date: 2000-11-30 14:44:25
Message-ID: 20001130094425.A17675@engsoc.queensu.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message martin.chantler 2000-11-30 14:50:04 Can PostGreSQL handle 100 user database?
Previous Message Gordan Bobic 2000-11-30 14:44:23 Re: Database cluster?