Search Postgresql Archives

Re: error in SELECT from store procedure

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux