Re: pg/sql functions field order

> The functions are there to prevent the same queries coming up over and

On first reading, I took the above to mean "the same [SQL] queries"
being repeated; at my second read, it seemed to refer to FAQs.
Now, I am not sure which, but the two issues intertwine.

I meant the first reading: they're there to prevent implementing (variants of) the same queries over and over again.

> 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.

I am on board with the function concept, just not up to speed on
the details.

If functions define the application, then should I be looking at
the main schema to find what the functions do?

Well, that's not practical...

The ~/doc/database/ contents seem dated. Should I rely on them?

... but, yea, since the contents of doc/database hasn't been regenerated for a while, it seems your only option for now. I'll see if I can get the documentation generated less

I find the double underscores hard to parse.

Apart from being word-separators, they have a function: they're supposed to be the separation between the namespace(alias 'module') and the actual function name within that namespace (application namespaces, not PostgreSQL namespaces; the latter being related to schemas).

> > 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,
I meant: I discount the value of my opinion because I am used to
using a different pattern and style.

Ah. Well, I respect all opinions highly, although we have to work out a common denominator, getting feedback on how people perceive the status quo is very important when deciding about the future direction --if and when such decisions are required.

> > 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

That was just an example. In a program, "select *" is a trap;
change a table definition and break your queries; I have not yet
found how you control that.

Ok. So, without knowing much about your prior experience, here's the introductory course on the functions:

Functions can return

1. SETOF <type>

2. <type>, where <type> can be

a. primitive

b. complex (a structure or row type)

Examples of each category (selected because they are likely to return results in your database too):

1. account__list_by_heading

2a. account__is_recon

2b. account__get_by_accno

(1) is like a (parameterized) view:

SELECT * FROM account__list_by_heading();

But also:

SELECT id, description FROM account__list_by_heading();

The statements above return rows with either all columns or the selected (named) columns in them. You can run these queries through Perl's DBI (and that's what LedgerSMB does!).

(2a) returns a single value.

SELECT account__is_recon('122321');

The above returns 1 row of 1 column. The value is of the type of the return value (boolean in this case).

(2b) returns different things, depending on the query you execute:

SELECT account__get_by_accno('122321');

returns a single row of 1 column, but that column holds a "record" type. This is basically an entire row inside a column. The other invocation option is:

SELECT <columns> FROM account__get_by_accno('122321');

This returns one row with multiple columns, normally accessible through DBI as you're used with table and view queries.

Erik, thanks, your reply has been helpful.

You're welcome. I'm sure there will be more questions. Don't hesitate to ask!

Regards,

Bye,

Erik.

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

Robust and Flexible. No vendor lock-in.