Обсуждение: PL/pgSQL: "record not assigned yet" meaning?

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?

Regards,
Ed Loehr

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@Yahoo.com #