Re: PL/pgSQL 1.2

2014-09-04 11:22 GMT+02:00 Joel Jacobson <joel(at)trustly(dot)com>:

> On Thu, Sep 4, 2014 at 11:07 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > it is different semantic - returns composite or set of composites ---
> it is
> > not row or rows
>
> The point was, RETURNS returns 1 while RETURNS SETOF returns 0 .. n.
>

no RETURNS return "VALUE" (it is not a row) .. and in combination with
SELECT - value will be a row. RETURNS SETOF returns rows

Set Returning Functions is interesting concept, but with some issues too -
when you use it in target part instead FROM part.

>
> > Actually BL is usually processed oriented, so PL functions coverages
> changes
> > in data, and for queries you use SELECT
>
> OK, so you SELECT directly from tables?
> And in the PLs you change a lot of rows in the same txn?
>

depends - if you be more strict, then direct access to tables is prohibited
and only access to views is enables.

There is simple rules: reading - selects to tables or views, writing PL --
data are changes inside some process and any process should be covered by
one or more PL

hard to say, how often you are change only one row maybe 50/50% -- when you
need fix some stored data. Insert or delete will be different

>
> > Returning SET from function is less often - and usually it is not in
> > preferred patterns because you can very simple block a optimizer.
>
> Not if you do all access, also SELECT via PLs, then you might want to
> returns
> lists of things based on some input.
>
> But that's a different topic. What I wanted to examplify is the fact
> we *already*
> have a lot of syntax which handles the 1 row case in a special way.
>

I know what is CRUD, and I looked to your functions from github and I
understand to your motivation. Just have different opinion about benefits
of some your proposal, because I use plpgsql little bit different. Using PL
only for CRUD is stopping in 1/10 way :).

Pavel