Re: [GENERAL] 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. > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings