Thread: Return Value of a Function
I need to return a row of data from a function. I've been looking the the HTML docs and have found nothing of value. If I try to return a variable of type RECORD, I get the following error: NOTICE: plpgsql: ERROR during compile of last_log near line 0 ERROR: fmgr_info: function 0: cache lookup failed I may have several other things wrong with this function, so my real question is, "Can I return a value of type RECORD?" -- Quote: 56 "Guard with jealous attention the public liberty. Suspect every one who approaches that jewel. Unfortunately, nothing will preserve it but down- right force. Whenever you give up that force, you are inevitably ruined." --Patrick Henry Work: 1-336-372-6812 Cell: 1-336-363-4719 email: terry@esc1.com
On Monday 02 February 2004 14:36, Terry Lee Tucker wrote: > > I may have several other things wrong with this function, so my real > question is, "Can I return a value of type RECORD?" Yes, but you can't necessarily do anything with it (unless you're calling it from another function). You could return a SETOF myrowtype where the set contains only one row - that's probably what you're after. Then you can do things like: SELECT * FROM set_returning_function(1,'a'); -- Richard Huxton Archonet Ltd
Terry Lee Tucker <terry@esc1.com> writes:
> I need to return a row of data from a function. I've been looking the the HTML
> docs and have found nothing of value. If I try to return a variable of type
> RECORD, I get the following error:
> NOTICE: plpgsql: ERROR during compile of last_log near line 0
> ERROR: fmgr_info: function 0: cache lookup failed
What Postgres version are you running? We used to have some bugs that
would allow unhelpful error messages like that to emerge in corner
cases. I'm not completely sure that they're all gone. If you're on
7.4.* I'd be interested to see exactly what you did.
> I may have several other things wrong with this function, so my real question
> is, "Can I return a value of type RECORD?"
If you know what you're doing. The calling query has to specify a
structure for the record.
(Joe, I couldn't find a self-contained example using a function-returning-
record after a bit of searching in the 7.4 docs. Surely there is one?)
regards, tom lane
Richard Huxton <dev@archonet.com> writes:
> You could return a SETOF myrowtype where the set contains only one row -
> that's probably what you're after. Then you can do things like:
> SELECT * FROM set_returning_function(1,'a');
You're confusing SETOF with returning a composite type --- actually they
are orthogonal features that can be used separately. Obviously the docs
could stand to be improved in this area :-(
regards, tom lane
Tom, My version is: 7.2.3-RH Is this my problem? The example you mention would be great to see if it can be located. I'm trying to write a function that will return the last record in a sequence of logs, "last" being defined by an ORDER BY statement containing time stamp criteria in conjuction with other data. The function would always be called by trigger code. Thanks... On Monday 02 February 2004 11:42 am, Tom Lane wrote: > Terry Lee Tucker <terry@esc1.com> writes: > > I need to return a row of data from a function. I've been looking the the > > HTML docs and have found nothing of value. If I try to return a variable > > of type RECORD, I get the following error: > > NOTICE: plpgsql: ERROR during compile of last_log near line 0 > > ERROR: fmgr_info: function 0: cache lookup failed > > What Postgres version are you running? We used to have some bugs that > would allow unhelpful error messages like that to emerge in corner > cases. I'm not completely sure that they're all gone. If you're on > 7.4.* I'd be interested to see exactly what you did. > > > I may have several other things wrong with this function, so my real > > question is, "Can I return a value of type RECORD?" > > If you know what you're doing. The calling query has to specify a > structure for the record. > > (Joe, I couldn't find a self-contained example using a function-returning- > record after a bit of searching in the 7.4 docs. Surely there is one?) > > regards, tom lane -- Work: 1-336-372-6812 Cell: 1-336-363-4719 email: terry@esc1.com
Tom Lane wrote: > (Joe, I couldn't find a self-contained example using a function-returning- > record after a bit of searching in the 7.4 docs. Surely there is one?) Looks like only one (see bottom of "Examples" section): http://www.postgresql.org/docs/current/static/sql-select.html Probably could use more examples somewhere. We frequently direct people to Stephan Szabo's "Set Returning Functions" page on Techdocs: http://techdocs.postgresql.org/guides/SetReturningFunctions Joe
Joe Conway <mail@joeconway.com> writes: > Tom Lane wrote: >> (Joe, I couldn't find a self-contained example using a function-returning- >> record after a bit of searching in the 7.4 docs. Surely there is one?) > Looks like only one (see bottom of "Examples" section): > http://www.postgresql.org/docs/current/static/sql-select.html > Probably could use more examples somewhere. Yeah, I would have expected to find functions-returning-RECORD discussed somewhere in xfunc.sgml, probably as a subsection near here: http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28789 regards, tom lane
Terry Lee Tucker wrote: > My version is: 7.2.3-RH That would be the first problem -- you need at least 7.3.x to return a composite type from PL/pgSQL. Might as well upgrade to 7.4.1 if you're going to do it at all. Joe
Terry Lee Tucker <terry@esc1.com> writes:
> My version is: 7.2.3-RH
> Is this my problem?
Probably. I don't recall the exact state of play of functions returning
rows in 7.2, but certainly Joe Conway has greatly improved it in the
last couple of releases. You should think about updating to 7.4.
> I'm trying to write a function that will return the last record in a
> sequence of logs, "last" being defined by an ORDER BY statement containing
> time stamp criteria in conjuction with other data. The function would always
> be called by trigger code.
You could try declaring the function to return the specific rowtype of
the log table, rather than the generic RECORD type. I'm quite certain
generic RECORD didn't do anything useful in 7.2. But even then, the
most useful way to call it (namely, a function call in SELECT's FROM
clause) wasn't there in 7.2.
Probably what you should do as long as you're on 7.2 is just have the
function determine and return the primary key of the correct log table
entry, and then SELECT using that key in the calling trigger functions.
regards, tom lane
We will be upgrading soon. Thanks for all the great advice. On Monday 02 February 2004 01:58 pm, Tom Lane wrote: > Terry Lee Tucker <terry@esc1.com> writes: > > My version is: 7.2.3-RH > > Is this my problem? > > Probably. I don't recall the exact state of play of functions returning > rows in 7.2, but certainly Joe Conway has greatly improved it in the > last couple of releases. You should think about updating to 7.4. > > > I'm trying to write a function that will return the last record in a > > sequence of logs, "last" being defined by an ORDER BY statement > > containing time stamp criteria in conjuction with other data. The > > function would always be called by trigger code. > > You could try declaring the function to return the specific rowtype of > the log table, rather than the generic RECORD type. I'm quite certain > generic RECORD didn't do anything useful in 7.2. But even then, the > most useful way to call it (namely, a function call in SELECT's FROM > clause) wasn't there in 7.2. > > Probably what you should do as long as you're on 7.2 is just have the > function determine and return the primary key of the correct log table > entry, and then SELECT using that key in the calling trigger functions. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Quote: 27 "The GOP Congress seems...resigned to grit its collective teeth and swallow a massive Medicare prescription drug benefit. Deep down, Republican lawmakers surely lack the appetite for this fat-drenched legislative entree. Yet they look obligated to finish it, as if leaving their meal untouched would be impolite. Instead, they should send this pricey dish back to the kitchen and order a snack instead." --Deroy Murdock Work: 1-336-372-6812 Cell: 1-336-363-4719 email: terry@esc1.com
I have some light examples in http://www.varlena.com/GeneralBits/Tidbits based on Stephan's. The discussion in issue http://www.varlena.com/GeneralBits/24.html shows the differences between setof and rows. --elein On Mon, Feb 02, 2004 at 09:47:11AM -0800, Joe Conway wrote: > Tom Lane wrote: > >(Joe, I couldn't find a self-contained example using a function-returning- > >record after a bit of searching in the 7.4 docs. Surely there is one?) > > Looks like only one (see bottom of "Examples" section): > http://www.postgresql.org/docs/current/static/sql-select.html > Probably could use more examples somewhere. > > We frequently direct people to Stephan Szabo's "Set Returning Functions" > page on Techdocs: > http://techdocs.postgresql.org/guides/SetReturningFunctions > > Joe > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match