"Ann W. Harrison" <aharrison@...> wrote:

>
> create procedure ....
> as
> declare variable a integer;
> extern transient data set y (f1 integer, f2 integer);
> begin
> <whatever>
> end

What if Y has another structure at the moment of procedure execution?
Runtime error? Even if they are compatible?

> > Second, I don't support a proposal to map existing DLTT/transient

dataset to

> > its local declaration. I believe it tends to cause structure mismatches

that

> > will bite everyone at runtime. Eevery structural change will require a

lot

> > of PSQL to be corrected as well. Too complex and risky to be used

without

> > worries.
>
> So you'd argue that transient data sets and procedures are simply
> incompatible and can not be made to work together.

They can in your mapping proposal, but IMHO this brings more issues than
benefits.

> Nested references are absolutely necessary for modular programming. We
> already see too many 1000+ line procedures. Adding a useful construct
> like a transient data set that can be used only in monolithic procedures
> pushes developers in the wrong direction.

A transient dataset which is not limited in scope is just a global which is
an evil in the structured programming.

> > Personally, I don't like a concept of nested visibility
> > of local objects (declared at upper levels),
>
> err... isn't that a fairly common programming language convention?

It's true for nested blocks, but not for nested calls. If I define an object
in procedure A and then call procedure B, the latter doesn't see my object
automagically. This is what I want for local datasets as well.

> Transient data sets are just another kind of table - restricted in scope
> to the connection, but otherwise just a table. Procedures see tables
> now without having them passed as formal parameters. I confused the
> issue by picking a bad key word and associating transient data sets with
> variables and cursors which are quite different objects.

But I considered them the same as I think PSQL-scoped tables are useful.

> > 1) temporary dataset can be created/declared in PSQL, but it's still an
> > independent session-scope object which is not destroyed upon procedure's
> > exit. It's visible in nested calls, but this is okay as it's not a local
> > object, it's just initialized in a procedure.

It's obvious I missed the BLR point here. We cannot just create/yield a
transient dataset and use it immediately in PSQL.

> Why do you restrict this to those transient data sets created in
> procedures? Why is using a transient data set from a procedure invoked
> by another procedure less fragile that using the same thing invoked from
> a application?

I didn't make any restrictions, I just didn't mention other uses of
transient datasets.

> > 2) temporary dataset is declared in PSQL and have a procedure-scope
> > visibility and lifecycle. It can be optionally initialized with a select
> > expression and/or can be later changed,
>
> What happens if it is created by an EXECUTE STATEMENT? How can you
> create procedure blr for something whose structure you don't know?

It cannot be declared with EXECUTE STATEMENT, but looks like it can be
populated with it. I don't remember whether we allow INSERTs against
transient datasets?

> > It can be passed to nested calls as an actual parameter
> > value (realistically thinking, this feature is a separate one).
>
> I'm not sure what you gain by passing the thing, rather than making it
> part of the name space, but sure, OK.

I'm just trying to avoid globals. And my proposal allows us to have really
PSQL-local temporary data (not declared by an application).

> OK. Part of the utility of transient data sets is that they can be
> easily refined, essentially replacing themselves... e.g.
>
> select e.name, e.age, e.salary from employees e yielding emp;
> select e.name, e.age, e.salary from emp e where e.age > 50 yielding emp;
> select e.name, e.age, e.salary from emp e
> where e.salary < (select average (e1.salary) from employees e1)
> yielding emp
>
> How does this sort of transformation work in your second model - where
> does the transient data set replaced itself, and where does it create a
> new version, visible only within the scope of the procedure?

as
declare transient data set emp (name, age, salary); // creates empty
dataset
begin
select e.name, e.age, e.salary from employees e yielding emp;
select e.name, e.age, e.salary from emp e where e.age > 50 yielding emp;
select e.name, e.age, e.salary from emp e
where e.salary < (select average (e1.salary) from employees e1)
yielding emp
end

or

as
declare transient data set emp (name, age, salary) as
select e.name, e.age, e.salary from employees e;
begin
select e.name, e.age, e.salary from emp e where e.age > 50 yielding emp;
select e.name, e.age, e.salary from emp e
where e.salary < (select average (e1.salary) from employees e1)
yielding emp
end

> > It's also possible to have both these technics, but it looks too much
> > complicated at the first glance.
>
> Interesting thought. Which variant is more useful?

After more thinking, I've became convienced that we need (a) session-level
transient datasets from your original proposal and (b) locally declared
PSQL-scoped transient datasets from my explanations above. Mapping between
global and local ones is possible, but IMO not desirable. Usage of global
ones without local mapping isn't technically possible.


Dmitry