Thread: error in SELECT from store procedure

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?

I.P.

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

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.
>