Return Value of a Function

[Terry Lee Tucker]

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: te***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

Terry Lee Tucker <te***@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

Richard Huxton <de*@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Terry Lee Tucker]

Tom,

My version is: 7.2.3-RH

Is this my problem? The example you mention would be great to see if it canbe
located. I'm trying to write a function that will return the last record ina
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 <te***@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: te***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Joe Conway]

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/curre...ql-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/guide...rningFunctions

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

[Tom Lane]

Joe Conway <ma**@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/curre...ql-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/curre....html#AEN28789

regards, tom lane

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

[Joe Conway]

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


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

[Tom Lane]

Terry Lee Tucker <te***@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 ma*******@postgresql.org)

[Terry Lee Tucker]

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 <te***@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 ma*******@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: te***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[elein]

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/curre...ql-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/guide...rningFunctions

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org