From: | John Gunther <postgresql(at)bucksvsbytes(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: why am I told "subquery must return only one column" (SELECTing values to be used as function arguments) |
Date: | 2008-05-05 18:10:35 |
Message-ID: | 481F4D9B.2000500@bucksvsbytes.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
<!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(dot)1210006864(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap="">John Gunther <a class="moz-txt-link-rfc2396E" href="mailto:postgresql(at)bucksvsbytes(dot)com"><postgresql(at)bucksvsbytes(dot)com></a> 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(dot)1210006864(at)sss(dot)pgh(dot)pa(dot)us" 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 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
</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>
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/html | 2.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Don Mies (NIM) | 2008-05-05 23:41:29 | Problem with Trigger |
Previous Message | Tom Lane | 2008-05-05 17:01:04 | Re: why am I told "subquery must return only one column" |