Re: pg/sql functions field order

Hi Rob,

On Sat, Aug 15, 2015 at 4:36 PM, R. Ransbottom <..hidden..> wrote:

Reading here, LSMB is to be based on pg/sql functions.
The idea is to cast the system in stone (functions) and
stop redundant or brittle sql popping up.

The functions are there to prevent the same queries coming up over and over. Now, you could argue that we should use views to prevent the duplication. Basically, that's what we do through the PL/pgSQL functions: we use them as parameterized views.

This rubs me
wrong a bit; using sql to analyse and make bulk changes
is one of the attractions of LSMB.

Well, you can still make bulk changes through SQL, but if you're using our PL/pgSQL functions for it, there are more guarantees that the changes agree with the expectations of the application.

The wrong may be that
I am used to my sql being set up from DBI.

I'm sorry, but I don't know what you mean by this remark, in the sense that calling the PL/pgSQL functions (or to be exact: stored procedures, some of which are PL/pgSQL and some of which are SQL) happens from sql statements too:

=> SELECT account__save(....);

=> SELECT * FROM account__list();

Given:
n5=> select account__get_from_accno( '1000');
account__get_from_accno
----------------------------------------------
(112,1000,"Checking Account",f,A,"",1,f,f,f)
(1 row)

I see that the field order is the same as a
"select *" or the order of definition in the
sql create statement.

Well, if you use the functions like this, then yes, I understand why you don't like this approach. But they're meant to be used differently. See below.

I don't know that I can count on that.

How am I _supposed_ to associate fieldnames with
the data?

Try:

select * from account__get_from_accno('1000');

Regards,

Bye,

Erik.

http://efficito.com -- Hosted accounting and ERP.

Robust and Flexible. No vendor lock-in.