Re: error in SELECT from store procedure

On Mon, Oct 10, 2005 at 06:43:25PM +0300, Ivan Pavlov wrote:
> I have a store procedure which returns a record; the query is defined as:
>
> select * from spec_proc.view_empl_1('bg',2) AS (f1 varchar, f2 varchar,
> f3 date, f4 varchar, f5 varchar,f6 varchar,f7 varchar, f8 varchar, f9
> int, f10 varchar, f11 varchar, f12 varchar, f13 int, f14 varchar, f15
> date, f16 date)
>
> I recieve the following error:
>
> ERROR: record "employee" is not assigned yet
> DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
>
> The record "employee" is the record returned by the store procedure.
> It is created after all other data are assigned to variables.
> Any ideas what might cause this?

Without seeing the function's code we can only guess. My first
guess is that a simplified version of the function would look
like this:

CREATE FUNCTION foo() RETURNS SETOF record AS $$
DECLARE
employee record;
BEGIN
employee.f1 := 'value of f1 column';
RETURN NEXT employee;
RETURN;
END;
$$ LANGUAGE plpgsql;

Calling this function yields the same error you're getting:

SELECT * FROM foo() AS (f1 varchar);
ERROR: record "employee" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT: PL/pgSQL function "foo" line 4 at assignment

See "Record Types" in the PL/pgSQL documentation for the reason:

http://www.postgresql.org/docs/8.0/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS

"The substructure of a record variable can change each time it is
assigned to. A consequence of this is that until a record variable
is first assigned to, it has no substructure, and any attempt to
access a field in it will draw a run-time error."

I'd guess you're making an assignment to a particular field instead
of to the record variable as a whole, so PL/pgSQL doesn't know what
the record structure should be. If that's the case, consider
creating a composite type and declaring employee to be of that type,
and perhaps also declare the function to return that type. Another
possibility would be to assign employee via a SELECT INTO statement.

--
Michael Fuhr