Обсуждение: SQL99 functions
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 assume that we would need to figure out how to generate multi-element tuples from the usual function calls, but wouldn't this dovetail with getting better SET return capabilities too? - Thomas
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. SurelySELECT 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. SurelySELECT 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
> > 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
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