Re: PL/pgSQL: "record not assigned yet" meaning?

Ed Loehr wrote:
> I have a plpgsql function that sets a column value on insert if it was not
> supplied:
>
> CREATE FUNCTION set_expense_unit()
> RETURNS OPAQUE AS
> 'DECLARE
> t RECORD;
> BEGIN
> if NEW.expense_unit_id isnull then
> SELECT INTO t eu.*
> FROM expense_unit eu, expense_type et
> WHERE et.id = NEW.expense_type_id
> AND et.expense_unit_id = eu.id;
>
> NEW.expense_unit_id := t.id;
> end if;
>
> RETURN NEW;
> END;'
> LANGUAGE 'plpgsql';
>
> DROP TRIGGER expense_insert_trigger ON expense;
> CREATE TRIGGER expense_insert_trigger
> BEFORE INSERT ON expense
> FOR EACH ROW
> EXECUTE PROCEDURE set_expense_unit();
>
> Each time it runs, I get this error:
>
> ERROR: record t is unassigned yet
>
> Any ideas what this means?

Means the "SELECT INTO t" failed to find a matching row.
You'd better check that with an

if NOT FOUND then
RAISE ...
end if;

directly after the SELECT to get your own, more appropriate
error message.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #