Re: PL/pgSQL 1.2

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

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

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

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

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;

Best Regards

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