Re: PL/pgSQL 1.2

2014-09-03 23:19 GMT+02:00 Hannu Krosing <hannu(at)2ndquadrant(dot)com>:

> On 09/03/2014 05:09 PM, Marko Tiikkaja wrote:
> > On 9/3/14 5:05 PM, Bruce Momjian wrote:
> >> On Wed, Sep 3, 2014 at 07:54:09AM +0200, Pavel Stehule wrote:
> >>> I am not against to improve a PL/pgSQL. And I repeat, what can be
> >>> done and can
> >>> be done early:
> >>>
> >>> a) ASSERT clause -- with some other modification to allow better
> >>> static analyze
> >>> of DML statements, and enforces checks in runtime.
> >>>
> >>> b) #option or PRAGMA clause with GUC with function scope that
> >>> enforce check on
> >>> processed rows after any DML statement
> >>>
> >>> c) maybe introduction automatic variable ROW_COUNT as shortcut for GET
> >>> DIAGNOSTICS rc = ROW_COUNT
> >>
> >> All these ideas are being captured somewhere, right? Where?
> >
> > I'm working on a wiki page with all these ideas. Some of them break
> > backwards compatibility somewhat blatantly, some of them could be
> > added into PL/PgSQL if we're okay with reserving a keyword for the
> > feature. All of them we think are necessary.
>
> Ok, here are my 0.5 cents worth of proposals for some features discussed
> in this thread
>
> They should be backwards compatible, but perhaps they are not very
> ADA/SQL-kosher ;)
>
> They also could be implemented as macros first with possible
> optimisations in the future
>
>
> 1. Conditions for number of rows returned by SELECT or touched by UPDATE
> or DELETE
>
> ---------------------------------------------------------------------------------------------------------
>
> Enforcing number of rows returned/affected could be done using the
> following syntax which is concise and clear (and should be in no way
> backwards incompatible)
>
> SELECT[1] - select exactly one row, anything else raises error
> SELECT[0:1] - select zero or one rows, anything else raises error
> SELECT[1:] - select one or more rows
>

It has zero verbosity and I don't like

>
> plain SELECT is equivalent to SELECT[0:]
>
> same syntax could be used for enforcing sane affected row counts
> for INSERT and DELETE
>
>
> A more SQL-ish way of doing the same could probably be called COMMAND
> CONSTRAINTS
> and look something like this
>
> SELECT
> ...
> CHECK (ROWCOUNT BETWEEN 0 AND 1);
>

It is very near to my proposed ASSERT

There is disadvantage of enhancing SQL syntax, because you have to handle
ugly in PLpgSQL parser or you have to push it to SQL parser.

SELECT ...; ASSERT CHECK ROWCOUNT BETWEEN 0 AND 1 .. solve it.

There is only one difference - ";" and we don't need to modify SQL and we
have total general solution

I don't like a design where is necessary to read documentation to language
with all small details first.

>
>
>
> 2. Substitute for EXECUTE with string manipulation
> ----------------------------------------------------------------
>
> using backticks `` for value/command substitution in SQL as an alternative
> to EXECUTE string
>
> Again it should be backwards compatible as , as currently `` are not
> allowed inside pl/pgsql functions
>
> Sample 1:
>
> ALTER USER `current_user` PASSWORD newpassword;
>
> would be expanded to
>
> EXECUTE 'ALTER USER ' || current_user ||
> ' PASSWORD = $1' USING newpassword;
>
> Sample2:
>
> SELECT * FROM `tablename` WHERE "`idcolumn`" = idvalue;
>
> this could be expanded to
>
> EXECUTE 'SELECT * FROM ' || tablename ||
> ' WHERE quote_ident(idcolumn) = $1' USING idvalue;
>
> Notice that the use of "" around `` forced use of quote_ident()
>

I am sorry - it is less readable than "format" function, and I afraid so
there is mental collision with MySQL wide used syntax.

Mainly - it is not natural solution that any beginner can do without
reading documentation. It is only shortcut, but not clear.

>
>
> 3. A way to tell pl/pggsql not to cache plans fro normal queries
>
> -----------------------------------------------------------------------------------
>
> This could be done using a #pragma or special /* NOPLANCACHE */
> comment as suggested by Pavel
>
>
In my experience - these special use cases can be wrapped well by function.
So we can use #option probably well

> Or we could expand the [] descriptor from 1. to allow more options
>
> OR we could do it in SQL-ish way using like this:
>
> SELECT
> ...
> USING FRESH PLAN;
>

Regards

Pavel

>
>
> Best Regards
>
> --
> Hannu Krosing
> PostgreSQL Consultant
> Performance, Scalability and High Availability
> 2ndQuadrant Nordic OÜ
>
>