<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane wrote:
<blockquote cite="mid:749.1210006864@[EMAIL PROTECTED]
" type="cite">
<pre wrap="">John Gunther <a class="moz-txt-link-rfc2396E"
href="mailto:postgresql@[EMAIL PROTECTED]
"><postgresql@[EMAIL PROTECTED]
>
writes:
</pre>
<blockquote type="cite">
<pre wrap="">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;
</pre>
</blockquote>
<pre wrap=""><!---->I think you want 'select sub(row(5,6,7,8)::m)'
</pre>
</blockquote>
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<br>
<blockquote cite="mid:749.1210006864@[EMAIL PROTECTED]
" type="cite">
<blockquote type="cite">
<pre wrap="">My question is this:
(select 5,6,7,8)::m returns a single entity of composite type m
</pre>
</blockquote>
<pre wrap=""><!---->
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 sup****t 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
</pre>
</blockquote>
I was extrapolating -- incorrectly I now know -- from Section 33.4.2:<br>
START QUOTE:<br>
<p> 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: </p>
<pre class="SCREEN">CREATE FUNCTION getname(emp) RETURNS text AS $$
SELECT $1.name;
$$ LANGUAGE SQL;
SELECT getname(new_emp());
getname
---------
None
(1 row)</pre>
END QUOTE<br>
<br>
Let me state my base problem then and perhaps you can point me to the
right technique:<br>
I have a function (sub, in the example) that takes 4 integers and does
a strictly arithmetic calculation to return a single numerical result.<br>
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.<br>
How can I pass the 4 numbers that mstr's select returns into the call
to sub?<br>
<br>
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.<br>
</body>
</html>
|
