Re: error in SELECT from store procedure

Thanks for the clarification. The problem turned out to be more trivial.
I was not doing something like: employee.f1 := 'value of f1 column'; as
you suggested, but declaring "employee" as record I did not pay
attention to the fact that I use a scheme named employee in the store
proc. My guess is that this caused the error I mentioned.

Thanks once again for your help

Ivan Pavlov

Michael Fuhr wrote:
> 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.
>