Return Value of a Function

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

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

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