Hi,
consider this function:

CREATE OR REPLACE FUNCTION FIND_USER_SOCKET_BYNAME (
IN IN_USERNAME VARCHAR,
OUT OUT_SOCKET_ADDRESS INTEGER)
AS
$BODY$
BEGIN
select socket_address from userdata where fullname = in_username into
out_socket_address;

if out_socket_address is null then
out_socket_address = 0 ;
end if;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE

If I call it like this:
select * from FIND_USER_SOCKET_BYNAME('juser');

I would expect to get back 1 value with the name of the OUT param
(OUT_SOCKET_ADDRESS).
However it comes back with the name of the function which I would expect
if I called it like this:

select FIND_USER_SOCKET_BYNAME('juser');

If I add another OUT value then the value comes back with the name of
the out param(plus the temp one I added) as expected.

It's easy enough to work around, but was not as expected.

Thanks,

Tony Caduto



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

http://archives.postgresql.org

Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> If I call it like this:
> select * from FIND_USER_SOCKET_BYNAME('juser');
> I would expect to get back 1 value with the name of the OUT param
> (OUT_SOCKET_ADDRESS).
> However it comes back with the name of the function

This is intentional, for compatibility with the pre-existing behavior
with functions in FROM. A function that isn't returning a record is
effectively declared as
FROM foo(...) AS foo(foo)
while a function that does return a record type gives you
FROM foo(...) AS foo(col1, col2)

regards, tom lane

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

Tom Lane wrote:

>Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
>
>
>>If I call it like this:
>>select * from FIND_USER_SOCKET_BYNAME('juser');
>>I would expect to get back 1 value with the name of the OUT param
>>(OUT_SOCKET_ADDRESS).
>>However it comes back with the name of the function
>>
>>
>
>This is intentional, for compatibility with the pre-existing behavior
>with functions in FROM. A function that isn't returning a record is
>effectively declared as
> FROM foo(...) AS foo(foo)
>while a function that does return a record type gives you
> FROM foo(...) AS foo(col1, col2)
>
> regards, tom lane
>
>
>
Tom,
Please don't take this the wrong way, but don't you think even if a
single param is declared as OUT it should return the name of the OUT param?

If the function has no OUT params and uses the return keyword it should
return the name of the function, if it has one or many out params it
should return even a single column as the name of the OUT param.

It seems inconsistant that just because I have one OUT param declared it
does not return the name I specified for that param.

Isn't it possible to detect that the function has a single OUT param
declared and if a OUT param is declared return that name?

I am bringing this up because people coming over from Oracle or MS SQL
server will notice something like this.

Thanks,

Tony Caduto




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

http://archives.postgresql.org

Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> Please don't take this the wrong way, but don't you think even if a
> single param is declared as OUT it should return the name of the OUT param?

Not really, because "create function foo (in x int, out y float)" is
supposed to have the same external behavior as "create function foo
(in x int) returns float". I agree it's a bit of a judgment call, but
I do not see a case for changing it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane wrote:
> Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> > Please don't take this the wrong way, but don't you think even if a
> > single param is declared as OUT it should return the name of the OUT param?
>
> Not really, because "create function foo (in x int, out y float)" is
> supposed to have the same external behavior as "create function foo
> (in x int) returns float". I agree it's a bit of a judgment call, but
> I do not see a case for changing it.

I am agreeing with the poster that use of OUT should always print the
out parameter name. Is there a downside to doing that? Seems it gives
people an option.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

Tom Lane wrote:

>Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
>
>
>>Please don't take this the wrong way, but don't you think even if a
>>single param is declared as OUT it should return the name of the OUT param?
>>
>>
>
>Not really, because "create function foo (in x int, out y float)" is
>supposed to have the same external behavior as "create function foo
>(in x int) returns float". I agree it's a bit of a judgment call, but
>I do not see a case for changing it.
>
> regards, tom lane
>
>

Hi Tom,
I understand where you are coming from, but I really think it should be
changed because that is how every other DB I know of works with a single
OUT param.

I was recently porting a fairly large application from
Firebird/Interbase and I had a bunch of functions that had one output
param, and in the win32 application that I was also moving over, it was
expecting the name of the OUT param, not the name of the function, So
either I change every single instance of the client code to now use the
function name or I add another dummy OUT param so my app does not have
to be modified.

The biggest reason to change this behavior is for porting from other
Databases so client code does not need to be needlessly modifed.

The new IN/OUT/INOUT params are sweet, and aside from this one issue, it
made porting the Firebird procs super easy.

I know I don't have much pull with development, but I think it should be
changed for the 8.1 release.

Thanks,

Tony




---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 9/29/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> > Please don't take this the wrong way, but don't you think even if a
> > single param is declared as OUT it should return the name of the OUT param?
>
> Not really, because "create function foo (in x int, out y float)" is
> supposed to have the same external behavior as "create function foo
> (in x int) returns float". I agree it's a bit of a judgment call, but
> I do not see a case for changing it.
>

Just my $0.02, but that seems inconsistent. In my mind, the
difference between functions with OUT params and functions that return
a RECORD (or a specific rowtype) is syntactic sugar. I'm pretty sure
that this was used to explain the implementation when it was being
discussed, in fact.

Using that logic, a functions with one OUT param would be the same as
a function returning a rowtype with only one column, and the one
column in such a rowtype certainly has a name of it's own.

--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org

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

Mike Rylander <mrylander@gmail.com> writes:
> Using that logic, a functions with one OUT param would be the same as
> a function returning a rowtype with only one column,

But it's not (and no, I don't want to make it so, because the overhead
for the useless record result would be significant).

regards, tom lane

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

Tom Lane wrote:

>Mike Rylander <mrylander@gmail.com> writes:
>
>
>>Using that logic, a functions with one OUT param would be the same as
>>a function returning a rowtype with only one column,
>>
>>
>
>But it's not (and no, I don't want to make it so, because the overhead
>for the useless record result would be significant).
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>
>
Tom,
I hardly think the overhead would be significant on modern processors, I
don't think the majority of users are running on Pentium 90s.( I am
assuming you mean a performance overhead)

The whole point is the current behavior is inconsistent and not expected
and should be changed to be inline with the way other DB systems work.
What is the point of even allowing a single OUT param then? You might
as well just raise a error and tell the user that a single OUT param is
not allowed.
8.1 is going to bring even more users over from systems like Firebird,
MS SQL and even Oracle, and all of these allow a single OUT param and it
returns the name of the OUT param, not the name of the function. Like I
said before this behavior is going to make it more difficult to port
applications from other systems.

How difficult can it be to check if the function has a single OUT param
as compared to the old way of using RETURN?

Sorry if I am being a pain in the you know what, but I really think I am
correct on this matter.


Thanks,

Tony

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

On Fri, Sep 30, 2005 at 10:20:34AM -0500, Tony Caduto wrote:
> Tom,
> I hardly think the overhead would be significant on modern processors, I
> don't think the majority of users are running on Pentium 90s.( I am
> assuming you mean a performance overhead)

Um, please read the documention. Returning a tuple is *significantly*
more expensive than returning a single value. You have to get the tuple
descriptor, allocate memory for the tuple, fill in all the fields with
your data... For a single value you just return it.

See here for all the details, you really don't want to do it if you
don't need to.

http://www.postgresql.org/docs/8.0/i....html#AEN30497

Now, you could fudge the parser to automatically alter the name of the
value in the function but I'm have no idea how hard that would be...
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDPV6fIB7bNG8LQkwRAny6AJ4iPEgTPx1i5b7vEDACWk kjllt5igCfY09X
FxgOf0dYWao8LPVWIt5JSOA=
=O7DC
-----END PGP SIGNATURE-----