SQL99 functions

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:

I see mention in SQL99 of function definitions which can have IN, OUT,
and INOUT arguments. Any thoughts on how this could be supported in
Postgres?

I noticed that but haven't quite figured out how it's supposed to fit
into the SQL worldview at all. Surely
SELECT foo(x) FROM table
shouldn't silently mutate into an UPDATE depending on how foo() is
declared. Exactly where is a function with OUT args useful in SQL?

regards, tom lane

Not knowing much on this side of things but in JDBC, CallableStatement
mentions things like IN, OUT & INOUT args - not sure about INOUT, but IN &
OUT are there. Perhaps OUT are not valid for selects, but are in stored
procedures?

Peter

--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, June 27, 2000 9:51 AM
To: Thomas Lockhart
Cc: Hackers List
Subject: Re: [HACKERS] SQL99 functions

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:

I see mention in SQL99 of function definitions which can have IN, OUT,
and INOUT arguments. Any thoughts on how this could be supported in
Postgres?

I noticed that but haven't quite figured out how it's supposed to fit
into the SQL worldview at all. Surely
SELECT foo(x) FROM table
shouldn't silently mutate into an UPDATE depending on how foo() is
declared. Exactly where is a function with OUT args useful in SQL?

regards, tom lane

Peter Mount wrote:

Not knowing much on this side of things but in JDBC, CallableStatement
mentions things like IN, OUT & INOUT args - not sure about INOUT, but IN &
OUT are there. Perhaps OUT are not valid for selects, but are in stored
procedures?

Peter

--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, June 27, 2000 9:51 AM
To: Thomas Lockhart
Cc: Hackers List
Subject: Re: [HACKERS] SQL99 functions

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:

I see mention in SQL99 of function definitions which can have IN, OUT,
and INOUT arguments. Any thoughts on how this could be supported in
Postgres?

I noticed that but haven't quite figured out how it's supposed to fit
into the SQL worldview at all. Surely
SELECT foo(x) FROM table
shouldn't silently mutate into an UPDATE depending on how foo() is
declared. Exactly where is a function with OUT args useful in SQL?

They are used (at least) in PL/SQL nad other PLs.

To return multiple values OUT args are one possible solution.

Also one could have session variables possibly like this:

DECLARE VARIABLE MY_VAR INT;

EXECUTE MY_PROC(MY_VAR);

SELECT * FROM SOME_TABLE WHERE ID=MY_VAR;

-----------
Hannu

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:

I see mention in SQL99 of function definitions which can

have IN, OUT,

and INOUT arguments. Any thoughts on how this could be supported in
Postgres?

I noticed that but haven't quite figured out how it's supposed to fit
into the SQL worldview at all. Surely
SELECT foo(x) FROM table
shouldn't silently mutate into an UPDATE depending on how foo() is
declared. Exactly where is a function with OUT args useful in SQL?

This is something Oracle pushed through, because that is how they do it.
I prefer the usual way of doing such things where you have parameters
and return values to functions (but return values should be multi column
and multi row capable).

Andreas

I see mention in SQL99 of function definitions which can have IN,
OUT, and INOUT arguments. Any thoughts on how this could be
supported in Postgres?

I noticed that but haven't quite figured out how it's supposed to fit
into the SQL worldview at all. Surely
SELECT foo(x) FROM table
shouldn't silently mutate into an UPDATE depending on how foo() is
declared. Exactly where is a function with OUT args useful in SQL?

create table t1 (x int);
create function foo (out int) returns int as ...;
select foo(x) from t1;

will give two columns for the result.

create function foo (inout int) returns int as ...;
select foo(x) from t1;

will mutate the result, but not the underlying stored value of t1.x.

Beware, I haven't yet confirmed this by reading ;)

- Thomas

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:

create table t1 (x int);
create function foo (out int) returns int as ...;
select foo(x) from t1;

will give two columns for the result.

You've *got* to be kidding.

To name just one problem with that, where do I put an AS to relabel
the extra column?

regards, tom lane

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:

create table t1 (x int);
create function foo (out int) returns int as ...;
select foo(x) from t1;

will give two columns for the result.

You've *got* to be kidding.

To name just one problem with that, where do I put an AS to relabel
the extra column?

It gets the name of the variable ( here "out"), no way to rename it,
but no problem since you can name the variable however you like.

Andreas

create function foo (out int) returns int as ...;
select foo(x) from t1;
will give two columns for the result.

You've *got* to be kidding.
To name just one problem with that, where do I put an AS to relabel
the extra column?

It gets the name of the variable ( here "out"), no way to rename it,
but no problem since you can name the variable however you like.

Actually, there is a third (optional) field which is the variable name:

create function foo (out pname int) returns int as ...;

Not sure what it should default to if the name is not specified.

- Thomas

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:

create table t1 (x int);
create function foo (out int) returns int as ...;
select foo(x) from t1;

will give two columns for the result.

You've *got* to be kidding.

To name just one problem with that, where do I put an AS to relabel
the extra column?

It gets the name of the variable ( here "out"), no way to rename it,
but no problem since you can name the variable however you like.

What? OUT is a keyword there; I don't see any explicit name for the
function's formal parameter at all.

BTW, what happens when I use foo() in a place other than the top level
of a SELECT list?

Examples:

SELECT (CASE WHEN x > 0 THEN foo(x) ELSE 42 END) FROM table

SELECT x FROM table WHERE foo(x) > 0

Also, I still haven't figured out what INOUT is supposed to do.

There are some things that are too brain-dead to consider implementing,
whether they are in SQL99 or not, and this is sounding a lot like
one of them...

regards, tom lane

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:

create table t1 (x int);
create function foo (out int) returns int as ...;
select foo(x) from t1;

will give two columns for the result.

You've *got* to be kidding.

To name just one problem with that, where do I put an AS to relabel
the extra column?

It gets the name of the variable ( here "out"), no way to rename it,
but no problem since you can name the variable however you like.

What? OUT is a keyword there; I don't see any explicit name for the
function's formal parameter at all.

To not look *that* stupid the mistake I made was:
I thought the parameter name was not optional

The default for in|out|"in out" is "in" thus I read the function as foo
("out" IN int)
(of course if it was an IN var it does'nt produce an out column and
thus need no label, mistake 2)

In Oracle the syntax is:
varname [IN|OUT|IN OUT] type

Andreas