Tom Lane wrote:
John Gunther <postgresql@bucksvsbytes.com> writes:
and trying to create a second function mstr that calls the sub function
using a select statement as sub's argument:
create function mstr(text,text) returns int as 'select sub((select
5,6,7,8)::m);' language sql;
I think you want 'select sub(row(5,6,7,8)::m)'
I didn't mention that the select statement in my example is a stand-in
for a much more complex one in the real function
My question is this:
(select 5,6,7,8)::m returns a single entity of composite type m
No, the select returns four columns, and trying to cast it after the
fact doesn't change that. We might at some point try to support the
above syntax, but I'm not in a big hurry considering that it's not
required by the SQL spec --- the row() syntax is what the spec says
you use to construct a composite value.
regards, tom lane
I was extrapolating -- incorrectly I now know -- from Section 33.4.2:
START QUOTE:
Another way to use a function returning a composite type is to pass
the result to another function that accepts the correct row type as
input:
CREATE FUNCTION getname(emp) RETURNS text AS $$
SELECT $1.name;
$$ LANGUAGE SQL;
SELECT getname(new_emp());
getname
---------
None
(1 row)
END QUOTE
Let me state my base problem then and perhaps you can point me to the
right technique:
I have a function (sub, in the example) that takes 4 integers and does
a strictly arithmetic calculation to return a single numerical result.
I then have a second function (mstr, in the example) that takes two
record ids, does a complex query to figure out sub's 4 integers, then
uses them to call sub and return its result.
How can I pass the 4 numbers that mstr's select returns into the call
to sub?
Obviously, I could break the select into 4 separate selects that return
one argument each but that seems terribly wasteful since I'd be
rerunning most of the original query 4 times.