Re: why am I told "subquery must return only one column" (SELECTing values to be used as function arguments)

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">&lt;postgresql(at)bucksvsbytes(dot)com&gt;</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

In response to

Browse pgsql-novice by date

  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"