Re: [HACKERS] PL/pgSQL 1.2

[Marko Tiikkaja]

On 2014-09-06 7:34 PM, Oskari Saarenmaa wrote:

Anyway, I think the discussed feature to make select, update and delete
throw an error if they returned or modified <> 1 row would be more
useful as an extension of the basic sql statements instead of a plpgsql
(2) only feature to make it possible to use it from other languages and
outside functions.


I can't really say I object to this, but doing it in the PL allows the 
parameters to be printed as well, akin to the 
plpgsql.print_strict_params setting added in 9.4.  Though I wonder if 
that would still be possible if PL/PgSQL peeked inside the parse tree a 
bit to pull out these constraints or something *waves hands*.  Or 
perhaps there's a better way to attach a helpful DETAIL line to the error.



.marko


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Oskari Saarenmaa]

06.09.2014 19:12, Jan Wieck kirjoitti:

On 09/06/2014 04:21 AM, Marko Tiikkaja wrote:

We wrap these things into (sometimes) simple-looking function so that
none of the application developers ever run any SQL.  We define an
interface between the application and the database, and that interface
is implemented using PL/PgSQL functions.  Sure, sometimes one function
will just fire off a single UPDATE .. RETURNING, or a SELECT, but that
doesn't matter.  The trick is to be consistent everywhere.


There is precisely your root problem. Instead of educating your
application developers on how to properly use a relational database
system, you try to make it foolproof.


There are also other reasons to wrap everything in functions, for 
example sharding using pl/proxy which by the way always throws an error 
if a SELECT didn't match exactly one row and the function wasn't 
declared returning 'SETOF' (although it currently doesn't set any 
sqlstate for these errors making it a bit difficult to properly catch them.)


Anyway, I think the discussed feature to make select, update and delete 
throw an error if they returned or modified <> 1 row would be more 
useful as an extension of the basic sql statements instead of a plpgsql 
(2) only feature to make it possible to use it from other languages and 
outside functions.


/ Oskari


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Marko Tiikkaja]

On 2014-09-06 6:12 PM, Jan Wieck wrote:

On 09/06/2014 04:21 AM, Marko Tiikkaja wrote:


We wrap these things into (sometimes) simple-looking function so that
none of the application developers ever run any SQL.  We define an
interface between the application and the database, and that interface
is implemented using PL/PgSQL functions.  Sure, sometimes one function
will just fire off a single UPDATE .. RETURNING, or a SELECT, but that
doesn't matter.  The trick is to be consistent everywhere.


There is precisely your root problem. Instead of educating your
application developers on how to properly use a relational database
system, you try to make it foolproof.


Foolproofing is just one thing that's good about this solution.  The 
other one would be that the application *doesn't need to know* what's 
going on behind the scenes.  The app deals with a consistent API, and we 
make that API happen with PL/PgSQL.



Guess what, the second you made something foolproof, evolution will
create a dumber fool. This is a race you cannot win.


You're completely missing the point.


.marko


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Jan Wieck]

On 09/06/2014 04:21 AM, Marko Tiikkaja wrote:


We wrap these things into (sometimes) simple-looking function so that
none of the application developers ever run any SQL.  We define an
interface between the application and the database, and that interface
is implemented using PL/PgSQL functions.  Sure, sometimes one function
will just fire off a single UPDATE .. RETURNING, or a SELECT, but that
doesn't matter.  The trick is to be consistent everywhere.


There is precisely your root problem. Instead of educating your 
application developers on how to properly use a relational database 
system, you try to make it foolproof.


Guess what, the second you made something foolproof, evolution will 
create a dumber fool. This is a race you cannot win.



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-06 15:12 GMT+02:00 Joel Jacobson :

> On Sat, Sep 6, 2014 at 6:59 AM, Pavel Stehule 
> wrote:
> > People can prepare a simple functions like you did:
> >
> > ...
> >
> > CREATE OR REPLACE FUNCTION user_list ()
> > RETURNS SETOF id AS $$
> > BEGIN
> >   RETURN QUERY SELECT id FROM user WHERE .. some = $1
> > END;
> > $$ LANGUAGE plpgsql;
> >
> > CREATE OR REPLACE FUNCTION update_user(int)
> > RETURNS void AS $$
> > BEGIN
> >   UPDATE user SET .. WHERE id = $1
> > END;
> > $$ LANGUAGE;
> >
> > And then  use it in mass operations:
> >
> > BEGIN
> >   FOR company IN SELECT * FROM company_list()
> >   LOOP
> > FOR id IN SELECT * FROM user_list(company)
> > LOOP
> >   update_user(id);
> > END LOOP;
> >
> > Or use it in application same style.
> >
> > It is safe .. sure, and I accept it. But It is terrible slow.
>
> The above is horrible and ugly. That's not how I write code.
> Only for top-level functions, i.e. API-functions, is it motivated to
> encapsulate even simple queries like that, but *never* in other
> PL-functions, as that doesn't fulfil any purpose, putting simple
> queries inside functions only make it less obvious what the code does
> where you have a function call instead of a SQL-query.
>

It is ugly, but I meet it. Its nothing special.

Pavel

Re: [HACKERS] PL/pgSQL 1.2

[Joel Jacobson]

On Sat, Sep 6, 2014 at 6:59 AM, Pavel Stehule  wrote:
> People can prepare a simple functions like you did:
>
> ...
>
> CREATE OR REPLACE FUNCTION user_list ()
> RETURNS SETOF id AS $$
> BEGIN
>   RETURN QUERY SELECT id FROM user WHERE .. some = $1
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE OR REPLACE FUNCTION update_user(int)
> RETURNS void AS $$
> BEGIN
>   UPDATE user SET .. WHERE id = $1
> END;
> $$ LANGUAGE;
>
> And then  use it in mass operations:
>
> BEGIN
>   FOR company IN SELECT * FROM company_list()
>   LOOP
> FOR id IN SELECT * FROM user_list(company)
> LOOP
>   update_user(id);
> END LOOP;
>
> Or use it in application same style.
>
> It is safe .. sure, and I accept it. But It is terrible slow.

The above is horrible and ugly. That's not how I write code.
Only for top-level functions, i.e. API-functions, is it motivated to
encapsulate even simple queries like that, but *never* in other
PL-functions, as that doesn't fulfil any purpose, putting simple
queries inside functions only make it less obvious what the code does
where you have a function call instead of a SQL-query.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Marko Tiikkaja]

(Forgot to answer to this part)

On 2014-09-06 06:59, Pavel Stehule wrote:

Your strategy is defensive. 100%. But then I don't understand to your
resistant  to verbosity. It is one basic stone of Ada design


I've never programmed in Ada, but I don't necessarily see why "more 
verbose" would unconditionally mean "more defensive".


My primary reason for objecting to some of the syntax suggestions that 
have been thrown around previously and during the last couple of days is 
that once you increase verbosity enough, the specialized syntax starts 
to be less and less desirable compared to what you can already do today. 
 And even that I only try to apply to the parts of the syntax I find 
verbose just for the sake of being verbose, i.e. without any additional 
functionality, disambiguity or clarity.  For example, having something 
like a  CONSTRAINT CHECK (row_count = 1);  is not really significantly 
better than   RETURNING TRUE INTO STRICT _OK.  It's better because the 
intent is more clear, and because you don't need a special _OK variable, 
but it still has 90% of the pain of the syntax you can use today.  That 
being the useless verbosity.



.marko


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Marko Tiikkaja]

On 2014-09-06 06:59, Pavel Stehule wrote:

People can prepare a simple functions like you did:

...

And then  use it in mass operations:

BEGIN
   FOR company IN SELECT * FROM company_list()
   LOOP
 FOR id IN SELECT * FROM user_list(company)
 LOOP
   update_user(id);
 END LOOP;

Or use it in application same style.


Yes, someone *could* do that, people are dumb.  But that's sort of 
*exactly* why we do it.


We wrap these things into (sometimes) simple-looking function so that 
none of the application developers ever run any SQL.  We define an 
interface between the application and the database, and that interface 
is implemented using PL/PgSQL functions.  Sure, sometimes one function 
will just fire off a single UPDATE .. RETURNING, or a SELECT, but that 
doesn't matter.  The trick is to be consistent everywhere.



But further, even if we did follow every single one of the above points
perfectly, it wouldn't change the point we're trying to make.  What we're
doing is following what the book dedicated an entire chapter to: Defensive
Programming.  Enforcing that that UPDATE affected exactly one row?
Defensive Programming.



Your strategy is defensive. 100%. But then I don't understand to your
resistant  to verbosity. It is one basic stone of Ada design

The problem of defensive strategy in stored procedures is possibility to
block optimizer and result can be terrible slow. On the end, it needs a
complex clustering solution, complex HA24 solution and higher complexity ~
less safety.

This is not problem on low load or low data applications.

Banking applications are safe (and I accept, so there it is necessary), but
they are not famous by speed.


Right.  We deal with money.  In general, I'll take slow over buggy any day.


.marko


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-06 4:25 GMT+02:00 Marko Tiikkaja :

> On 2014-09-04 2:28 PM, I wrote:
>
>> On 9/4/14 2:04 PM, Pavel Stehule wrote:
>>
>>> for example best practices for PL/SQL by Steven Feuerstein
>>>
>>
>> I'll spend some time with that book to have a better idea on where
>> you're coming from.
>>
>
> I've read through this book twice now.  Some observations on things we
> don't follow:
>
>   - We don't use the exact hungarian notation -ish convention for naming
> stuff.  I don't see that as a bad thing.
>   - Granted, we could be using the  myfield tablename.columnname%TYPE;
> probably more.  On the other hand, sometimes you would prefer to not have
> all your types in your functions change transparently after an ALTER TABLE.
>   - The book takes the "single exit point" thinking to an extreme.  I
> don't agree with that, regardless of the language (and thus I might not
> necessarily always follow it).
>   - The book says "Encapsulate INSERT, UPDATE, and DELETE statements
> behind procedure calls", which quite directly contradicts what you said
> earlier.
>

Not necessary -- It say -- complex SQL should not be used more times in
code, but there is not specified, so they must by stored in trivial
functions. Complex queries should be wrapped by views instead - it doesn't
block a optimizer

There is a strong warning to not break optimizer.


>
> The rest of the stuff we follow in our codebase as far as I can tell
> (except the Oracle-specific stuff, obviously).
>

Ten years ago I wrote article
http://postgres.cz/wiki/PL/pgSQL_%28en%29#Recommendation_for_design_of_stored_procedures_in_PL.2FpqSQL_language
based on Steve F, Joe Celko and others presentations and books


http://postgres.cz/wiki/PL/pgSQL_%28en%29#Recommendation_for_design_of_stored_procedures_in_PL.2FpqSQL_language

There is point: "Don't enclose SQL commands to simply functions uselessly."

Where is a problem.

People can prepare a simple functions like you did:

...

CREATE OR REPLACE FUNCTION user_list ()
RETURNS SETOF id AS $$
BEGIN
  RETURN QUERY SELECT id FROM user WHERE .. some = $1
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_user(int)
RETURNS void AS $$
BEGIN
  UPDATE user SET .. WHERE id = $1
END;
$$ LANGUAGE;

And then  use it in mass operations:

BEGIN
  FOR company IN SELECT * FROM company_list()
  LOOP
FOR id IN SELECT * FROM user_list(company)
LOOP
  update_user(id);
END LOOP;

Or use it in application same style.

It is safe .. sure, and I accept it. But It is terrible slow.

If you are lucky and have some knowledges, you can use a SQL function in
Postgres. It is a macros, so it is not a black bock for optimizer, but I am
not sure, if postgres optimizer can do well work in this case too.

This is Joe Celko lovely theme.


> But further, even if we did follow every single one of the above points
> perfectly, it wouldn't change the point we're trying to make.  What we're
> doing is following what the book dedicated an entire chapter to: Defensive
> Programming.  Enforcing that that UPDATE affected exactly one row?
> Defensive Programming.
>

Your strategy is defensive. 100%. But then I don't understand to your
resistant  to verbosity. It is one basic stone of Ada design

The problem of defensive strategy in stored procedures is possibility to
block optimizer and result can be terrible slow. On the end, it needs a
complex clustering solution, complex HA24 solution and higher complexity ~
less safety.

This is not problem on low load or low data applications.

Banking applications are safe (and I accept, so there it is necessary), but
they are not famous by speed.

Pavel


>
>
> .marko
>

Re: [HACKERS] PL/pgSQL 1.2

[Marko Tiikkaja]

On 2014-09-04 2:28 PM, I wrote:

On 9/4/14 2:04 PM, Pavel Stehule wrote:

for example best practices for PL/SQL by Steven Feuerstein


I'll spend some time with that book to have a better idea on where
you're coming from.


I've read through this book twice now.  Some observations on things we 
don't follow:


  - We don't use the exact hungarian notation -ish convention for 
naming stuff.  I don't see that as a bad thing.
  - Granted, we could be using the  myfield tablename.columnname%TYPE; 
 probably more.  On the other hand, sometimes you would prefer to not 
have all your types in your functions change transparently after an 
ALTER TABLE.
  - The book takes the "single exit point" thinking to an extreme.  I 
don't agree with that, regardless of the language (and thus I might not 
necessarily always follow it).
  - The book says "Encapsulate INSERT, UPDATE, and DELETE statements 
behind procedure calls", which quite directly contradicts what you said 
earlier.


The rest of the stuff we follow in our codebase as far as I can tell 
(except the Oracle-specific stuff, obviously).


But further, even if we did follow every single one of the above points 
perfectly, it wouldn't change the point we're trying to make.  What 
we're doing is following what the book dedicated an entire chapter to: 
Defensive Programming.  Enforcing that that UPDATE affected exactly one 
row?  Defensive Programming.



.marko


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Joel Jacobson]

On Thu, Sep 4, 2014 at 5:51 PM, Robert Haas  wrote:
>> When you suggest ISAM, that's like saying "demolish your house and
>> build a new one" when all I want is to make small but important
>> changes to what I already do as a professional on a daily basis.
>
> Go right ahead: this is an open source project, after all, and with an
> extremely permissive license to boot.  You can modify your copy of
> PL/pgsql, or clone it and make PL/joelsql and then change whatever you
> like.  Optionally, you could then publish that on PGXN for others to
> use and contribute to.
>
> On the other hand, if what you want is for other people to make
> changes to the official versions of PostgreSQL that are supported and
> maintained by the community, then that's a different thing altogether.
> It entails two challenges: first, to persuade the community that those
> changes will be good for everyone, not just you; and second,
> convincing them that they (rather than you) should be the ones to do
> the work.  So far I'd say you're losing the first argument, and I
> expect you'll lose the second one, too (barring a financial
> transaction, of course).
>
> I'm not trying to brush you off here - I understand your concerns, and
> they're not stupid.  But, like most of the people who have commented,
> I don't agree that your proposals would be an improvement for the
> majority of people.  There are several ways to deal with that, but if
> your goal is to get those changes made in the PostgreSQL community
> then you have to acknowledge the competing concerns to be just as
> valid as your own and come up with a proposal everyone can live with.

If my company would write code in PL/joelsql, I think I would have a hard
time through any technical due diligence in the future. :-)

The main reason why I'm so eager of finding a support from you,
the majority of other readers on this list, is of course because I think
we as a group can come up with a much better solution to the problem
than what I could on my own. And for me it's better if we can agree on
*something* which improves my and others life to *some* extent,
rather than to just sitting here silent waiting another 16 years for
PL/pgSQL 2 to
develop itself.

I can certainly live with a more SQLish syntax than the one I had in mind.

I'm less concerned about the verbosity of the language, if I wanted a condensed
language I should have opted for some other language in the first place,
so that's not my problem.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 18:02 GMT+02:00 Kevin Grittner :

> Pavel Stehule  wrote:
>
> > You just need a ISAM API for Postgres, That is all.
>
> Joel sure hasn't *shown* us anything to suggest that wouldn't
> answer his needs better than any PL, or explained why that wouldn't
> be a better solution for him.
>

I understand what Joel does. And there is a space for improvement of
plpgsql - on syntax level, on internal level. But we can start with some
less controversial.

And some controversial points we can coverage by extensions. It is in
conformance with Postgres community politics - where is not agreement, use
extensions. We have to be able to write these extensions.

Extensibility of plpgsql is on the begin. But for some special use cases,
these extensions can be perfect.

>From this long discuss I am thinking so there is perfect agreement on
plpgsql asserts. We needed. And now we know where assertations can be used.
There is agreement on using binary casting instead IO casting every where
where it is possible. And I am not against to ensuring consistent behave of
assigning, returning from fce for composite types. There is small
differences between rows, records, .. But should not be too hurry. There
are only few people who would to changes in this area. Almost users are
happy.

Personally I would to see a discussion about enhancing SPI much more --
because it is base of all PL and some performance limits and some internal
complexity of plpgsql (and plpgsql_check too) is based on missing some
interface between SPI and PL.

Regards

Pavel


>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: [HACKERS] PL/pgSQL 1.2

[Kevin Grittner]

Pavel Stehule  wrote:

> You just need a ISAM API for Postgres, That is all.

Joel sure hasn't *shown* us anything to suggest that wouldn't
answer his needs better than any PL, or explained why that wouldn't
be a better solution for him.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Robert Haas]

On Thu, Sep 4, 2014 at 11:32 AM, Joel Jacobson  wrote:
>> On 4 sep 2014, at 17:18, Pavel Stehule  wrote:
>>
>> You just need a ISAM API for Postgres, That is all.
>
> Now you are being ironic, and I would prefer to keep the discussion on
> a serious level. You know that's not applicable in my case, you know
> what I do for work and what kind of system we already have.
>
> I *love* plpgsql and our development method. I just want it to get
> slightly more convenient and secure.
>
> When you suggest ISAM, that's like saying "demolish your house and
> build a new one" when all I want is to make small but important
> changes to what I already do as a professional on a daily basis.

Go right ahead: this is an open source project, after all, and with an
extremely permissive license to boot.  You can modify your copy of
PL/pgsql, or clone it and make PL/joelsql and then change whatever you
like.  Optionally, you could then publish that on PGXN for others to
use and contribute to.

On the other hand, if what you want is for other people to make
changes to the official versions of PostgreSQL that are supported and
maintained by the community, then that's a different thing altogether.
It entails two challenges: first, to persuade the community that those
changes will be good for everyone, not just you; and second,
convincing them that they (rather than you) should be the ones to do
the work.  So far I'd say you're losing the first argument, and I
expect you'll lose the second one, too (barring a financial
transaction, of course).

I'm not trying to brush you off here - I understand your concerns, and
they're not stupid.  But, like most of the people who have commented,
I don't agree that your proposals would be an improvement for the
majority of people.  There are several ways to deal with that, but if
your goal is to get those changes made in the PostgreSQL community
then you have to acknowledge the competing concerns to be just as
valid as your own and come up with a proposal everyone can live with.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Jan Wieck]

On 09/04/2014 11:16 AM, Joel Jacobson wrote:

On 4 sep 2014, at 16:45, Hannu Krosing  wrote:

When looking from the other end of the problem, we are
using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql
when we really want scalars.

My understanding is that one main drivers of starting this thread
was wanting also guaranteed SCALAR versions of these.

And wanting them in a way that is easy to use.


+1

Thank you! I have been trying to explain this in multiple cryptic ways
but failed. You just nailed it! That's *exactly* what I mean!


I believe we all agree that the availability of most of the proposed 
functionality is desirable.


I think the main difference between your point of view and that of a few 
others (me included) is that you prefer a language that is easy and fast 
to type, with as few key strokes as possible, while we prefer a language 
that is similar to SQL, which is rather verbose to the reader. At least 
when the discussion is about the default procedural language installed 
with the core database system.


Such a language should be as similar as possible to SQL. Which is the 
reason why I believe that the CHECK clause belongs into the main parser, 
not into the PL.



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Joel Jacobson]

> On 4 sep 2014, at 17:18, Pavel Stehule  wrote:
>
> You just need a ISAM API for Postgres, That is all.

Now you are being ironic, and I would prefer to keep the discussion on
a serious level. You know that's not applicable in my case, you know
what I do for work and what kind of system we already have.

I *love* plpgsql and our development method. I just want it to get
slightly more convenient and secure.

When you suggest ISAM, that's like saying "demolish your house and
build a new one" when all I want is to make small but important
changes to what I already do as a professional on a daily basis.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 17:10 GMT+02:00 Joel Jacobson :

>
>
> On 4 sep 2014, at 15:32, Pavel Stehule  wrote:
>
>
>
>
> 2014-09-04 15:24 GMT+02:00 Jan Wieck :
>
>> On 09/04/2014 01:14 AM, Pavel Stehule wrote:
>>
>>> 2014-09-03 23:19 GMT+02:00 Hannu Krosing >> 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
>>>
>>
>> Only if the ASSERT syntax would become part of the original statement, it
>> is supposed to check. In Hannu's command constraint example above, the
>> statement that causes the error, and thus will be logged and become
>> identified by the error message, is the actual SELECT (or other DML
>> statement).
>>
>
> this is valid argument.
>
> On second hand, I proposed a ASSERT that was not based on expressions
> only. There is not a technical issue to write assert with knowledge of
> related statement.
>
>
>>
>> I think I like the COMMAND CONSTRAINT the best so far.
>>
>
> I not, because when it will not be part of SQL, than parser in plpgsql
> will be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE
>
>
> This is what I suspected. You are against the best syntax because they are
> more complex to implement. I think that's coming into the discussion from
> the wrong direction. First agree on the best syntax, then worry about the
> implementation.
>
>
Nobody say here, so it is best syntax. It is request of proprietary
enhancing of SQL and lot of people say strongly no. But you don't listen.


> I also understand the syntax changes will mean a lot of trouble for your
> plpgsql_check_function() project, but that cannot hold us back, we must aim
> for the best possible syntax with plpgsql2.
>  Your work with plpgsql_check_function() btw saved me hundreds of hours of
> work, when we upgraded from 8.4 a few years ago, many thanks Pavel!
>

I have no problem with plpgsql_check_function management. I remember well
how issues is related to support plpgsql specific STRICT or INTO clauses.

Pavel




>
>
> Pavel
>
>
>>
>>
>> Regards,
>> Jan
>>
>> --
>> Jan Wieck
>> Senior Software Engineer
>> http://slony.info
>>
>
>

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 17:16 GMT+02:00 Joel Jacobson :

> > On 4 sep 2014, at 16:45, Hannu Krosing  wrote:
> >
> > When looking from the other end of the problem, we are
> > using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql
> > when we really want scalars.
> >
> > My understanding is that one main drivers of starting this thread
> > was wanting also guaranteed SCALAR versions of these.
> >
> > And wanting them in a way that is easy to use.
>
> +1
>
> Thank you! I have been trying to explain this in multiple cryptic ways
> but failed. You just nailed it! That's *exactly* what I mean!
>

You just need a ISAM API for Postgres, That is all.

Pavel


>
> Thanks for clarifying!
>
> >
> >
> > Cheers
> >
> >
> > --
> > Hannu Krosing
> > PostgreSQL Consultant
> > Performance, Scalability and High Availability
> > 2ndQuadrant Nordic OÜ
>

Re: [HACKERS] PL/pgSQL 1.2

[Joel Jacobson]

> On 4 sep 2014, at 16:45, Hannu Krosing  wrote:
>
> When looking from the other end of the problem, we are
> using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql
> when we really want scalars.
>
> My understanding is that one main drivers of starting this thread
> was wanting also guaranteed SCALAR versions of these.
>
> And wanting them in a way that is easy to use.

+1

Thank you! I have been trying to explain this in multiple cryptic ways
but failed. You just nailed it! That's *exactly* what I mean!

Thanks for clarifying!

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Joel Jacobson]

On 4 sep 2014, at 15:32, Pavel Stehule  wrote:




2014-09-04 15:24 GMT+02:00 Jan Wieck :

> On 09/04/2014 01:14 AM, Pavel Stehule wrote:
>
>> 2014-09-03 23:19 GMT+02:00 Hannu Krosing > 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
>>
>
> Only if the ASSERT syntax would become part of the original statement, it
> is supposed to check. In Hannu's command constraint example above, the
> statement that causes the error, and thus will be logged and become
> identified by the error message, is the actual SELECT (or other DML
> statement).
>

this is valid argument.

On second hand, I proposed a ASSERT that was not based on expressions only.
There is not a technical issue to write assert with knowledge of related
statement.


>
> I think I like the COMMAND CONSTRAINT the best so far.
>

I not, because when it will not be part of SQL, than parser in plpgsql will
be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE


This is what I suspected. You are against the best syntax because they are
more complex to implement. I think that's coming into the discussion from
the wrong direction. First agree on the best syntax, then worry about the
implementation.

I also understand the syntax changes will mean a lot of trouble for your
plpgsql_check_function() project, but that cannot hold us back, we must aim
for the best possible syntax with plpgsql2.
Your work with plpgsql_check_function() btw saved me hundreds of hours of
work, when we upgraded from 8.4 a few years ago, many thanks Pavel!


Pavel


>
>
> Regards,
> Jan
>
> --
> Jan Wieck
> Senior Software Engineer
> http://slony.info
>

Re: [HACKERS] PL/pgSQL 1.2

[Hannu Krosing]

On 09/04/2014 02:40 PM, Pavel Stehule wrote:
>
>
>
> 2014-09-04 14:37 GMT+02:00 Joel Jacobson  >:
>
>
>
> On 4 sep 2014, at 11:42, Pavel Stehule  > wrote:
>> 2014-09-04 11:22 GMT+02:00 Joel Jacobson > >:
>>
>> 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
>
> I intentionally excluded the data type of what is returned.
> 1 "VALUE" vs 0...n "VALUES"
> Do you still fail to see the point 1 "VALUE" is special in the
> context of what a function returns?
>
>
> sorry, I don't understand .. for me SRF functions are absolutly
> different monsters than scalar, array or composite function - so its
> impossible to compare it.
When looking from the other end of the problem, we are
using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql
when we really want scalars.

My understanding is that one main drivers of starting this thread
was wanting also guaranteed SCALAR versions of these.

And wanting them in a way that is easy to use.


Cheers


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

Re: [HACKERS] PL/pgSQL 1.2

[Marko Tiikkaja]

On 9/4/14 4:09 PM, Shaun Thomas wrote:

On 09/03/2014 04:19 PM, Hannu Krosing wrote:


1. Conditions for number of rows returned by SELECT or touched by
UPDATE or DELETE


Now that I think upon this... don't we already have it?

SELECT ... LIMIT 1


No, that just hides any bugs.  We want the opposite: any bugs or 
problems should be obvious.  If the query returns or touches more than 
one row, that should raise an error, not just give you a random one and 
call it a day.



That already solves the purported problem of multiple results in SELECT
INTO as well. Could we possibly extend that to UPDATE and DELETE syntax too?


Again, this is a different problem, but LIMIT syntax for UPDATE and 
DELETE has been proposed, see: 
http://www.postgresql.org/message-id/CADB9FDf-Vh6RnKAMZ4Rrg_YP9p3THdPbji8qe4qkxRuiOwm=m...@mail.gmail.com



.marko


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Shaun Thomas]

On 09/03/2014 04:19 PM, Hannu Krosing wrote:


1. Conditions for number of rows returned by SELECT or touched by
UPDATE or DELETE


Now that I think upon this... don't we already have it?

SELECT ... LIMIT 1

That already solves the purported problem of multiple results in SELECT 
INTO as well. Could we possibly extend that to UPDATE and DELETE syntax too?


--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 15:38 GMT+02:00 Jan Wieck :

> On 09/04/2014 09:31 AM, Pavel Stehule wrote:
>
>> 2014-09-04 15:24 GMT+02:00 Jan Wieck >
>> I think I like the COMMAND CONSTRAINT the best so far.
>>
>>
>> I not, because when it will not be part of SQL, than parser in plpgsql
>> will be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE
>>
>
> Making the COMMAND CONSTRAINT part of the core SQL parser was how I
> understood Hannu's idea. It would be horrible to tuck that feature away
> inside of a PL, rather than making it available to all PLs as well as
> applications, that use SQL directly (I think there still are two or three
> applications that do).


So I am happy so we have agreement, so implementation on PL level can be
terrible.

Pavel



>
>
>
> Regards,
> Jan
>
> --
> Jan Wieck
> Senior Software Engineer
> http://slony.info
>

Re: [HACKERS] PL/pgSQL 1.2

[Jan Wieck]

On 09/04/2014 09:31 AM, Pavel Stehule wrote:

2014-09-04 15:24 GMT+02:00 Jan Wieck 

Making the COMMAND CONSTRAINT part of the core SQL parser was how I 
understood Hannu's idea. It would be horrible to tuck that feature away 
inside of a PL, rather than making it available to all PLs as well as 
applications, that use SQL directly (I think there still are two or 
three applications that do).



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 15:24 GMT+02:00 Jan Wieck :

> On 09/04/2014 01:14 AM, Pavel Stehule wrote:
>
>> 2014-09-03 23:19 GMT+02:00 Hannu Krosing > 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
>>
>
> Only if the ASSERT syntax would become part of the original statement, it
> is supposed to check. In Hannu's command constraint example above, the
> statement that causes the error, and thus will be logged and become
> identified by the error message, is the actual SELECT (or other DML
> statement).
>

this is valid argument.

On second hand, I proposed a ASSERT that was not based on expressions only.
There is not a technical issue to write assert with knowledge of related
statement.


>
> I think I like the COMMAND CONSTRAINT the best so far.
>

I not, because when it will not be part of SQL, than parser in plpgsql will
be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE

Pavel


>
>
> Regards,
> Jan
>
> --
> Jan Wieck
> Senior Software Engineer
> http://slony.info
>

Re: [HACKERS] PL/pgSQL 1.2

[Jan Wieck]

On 09/04/2014 01:14 AM, Pavel Stehule wrote:

2014-09-03 23:19 GMT+02:00 Hannu Krosing 

Only if the ASSERT syntax would become part of the original statement, 
it is supposed to check. In Hannu's command constraint example above, 
the statement that causes the error, and thus will be logged and become 
identified by the error message, is the actual SELECT (or other DML 
statement).


I think I like the COMMAND CONSTRAINT the best so far.


Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 14:37 GMT+02:00 Joel Jacobson :

>
>
> On 4 sep 2014, at 11:42, Pavel Stehule  wrote:
>
> 2014-09-04 11:22 GMT+02:00 Joel Jacobson :
>
>> 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
>
>
> I intentionally excluded the data type of what is returned.
> 1 "VALUE" vs 0...n "VALUES"
> Do you still fail to see the point 1 "VALUE" is special in the context of
> what a function returns?
>

sorry, I don't understand .. for me SRF functions are absolutly different
monsters than scalar, array or composite function - so its impossible to
compare it.

Pavel

Re: [HACKERS] PL/pgSQL 1.2

[Joel Jacobson]

On 4 sep 2014, at 11:42, Pavel Stehule  wrote:

2014-09-04 11:22 GMT+02:00 Joel Jacobson :
>
> 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


I intentionally excluded the data type of what is returned.
1 "VALUE" vs 0...n "VALUES"
Do you still fail to see the point 1 "VALUE" is special in the context of
what a function returns?

Re: [HACKERS] PL/pgSQL 1.2

[Marko Tiikkaja]

On 9/4/14 2:04 PM, Pavel Stehule wrote:

for example best practices for PL/SQL by Steven Feuerstein


I'll spend some time with that book to have a better idea on where 
you're coming from.


Also, *please* don't try and extrapolate what I do based on the code 
examples on the wiki page; they're all crap just to point out the issues.



We can talk about it - it subjective and I know so there are not only one
style.

The language has these possibilities. Why to add new redundant?


Adding a new alias for every single OUT parameter for every single 
function seems like a waste of time.  It also doesn't improve 
readability in the way that  OUT.foo := 1;  does (though I guess you 
could add an "out_" prefix to all of them).



.marko


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Robert Haas]

On Thu, Sep 4, 2014 at 4:06 AM, Joel Jacobson  wrote:
> Your "ASSERT CHECK ROWCOUNT = 1;" is lengthly, which is why I don't like it.
> Imagine if having to type
> my $var === 'foo';
> instead of
> my $var = 'foo';
> on every single line of could where you want to assign a variable,
> that would just be ridiculous.

This is really the core of the problem.  PL/pgsql is an incredibly
verbose language, and things that ought to be expressable in a small
number of characters often take a large number of characters.  The
reason this problem is hard to solve is that PL/pgsql is based on SQL,
and SQL is inherently pretty verbose.  When we try to extend PL/pgsql,
we can either pick syntax that looks like the existing language (which
sucks because it's verbose) or we can pick syntax that is compact
(which sucks because it looks nothing like the rest of the language).
This is a deep and fundamental conflict that is not going away.

So I'm with the crowd of people who says there's really nothing that
can be done to improve PL/pgsql much.  Yeah, we could make certain
cases a little better at the expense of certain other cases (which is
how this thread got started), but it's not really clear that we'd end
up ahead at all (since a lot of people objected to the proposed
changes) and if we did we wouldn't end up very far ahead (since the
original proposal consisted of three minor items which are not going
to radically transform anyone's experience).  Even Oracle, who has a
vastly larger R&D budget than the PostgreSQL community, frankly hasn't
done all that much with it.  They have some nice conveniences which we
lack, but it's still a clunky language.

So, I think the right solution here is to work on improving the user
experience people have with other procedural languages.  Perl, Python,
and Javascript are all notable for being terse, sometimes to excess.
I remember trying to use PL/perl many years ago and giving up on it
because of various problems, like not being able to get "require" to
work, and not being able to create helper functions that could be
called directly from Perl without going back through the SQL layer.
But some of those kinds of things may have been fixed between then and
now, and whichever ones haven't probably can be if people are willing
to put in some work.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 13:54 GMT+02:00 Marko Tiikkaja :

> On 9/4/14 1:47 PM, Pavel Stehule wrote:
>
>> 2014-09-04 13:37 GMT+02:00 Marko Tiikkaja :
>>
>>> I've started a wiki page with the list of the things I could think of at
>>>
>>> this very moment.  I probably got the most annoying ones in there, but I
>>> also might have forgotten about some things.  I invite discussion of
>>> every
>>> suggestion on -HACKERS.
>>>
>>
>> where I can wrote comments?
>>
>
> I guess comments could be added as a subsection if you feel -HACKERS
> doesn't work.


ok


>
>
>  I am sorry, It is difficult - your usage of plpgsql is sometimes less,
>> some
>> times more against best practices :(
>>
>
> Best practices according to whom?  And which parts, exactly?  There's
> quite a lot of stuff in there, both explicitly stated and implicitly
> assumed.
>
>
>  PL functions should not be only envelope to SQL statement
>>
>
> I disagree, to some extent.  Our "external" applications interface with
> the database only by calling functions (which are often written in
> PL/PgSQL).  If that function has no more work to do than to run a single
> query, then yes, the function will just serve as an envelope to a single
> query.  But that also allows the function to be changed in the future
> without having to change the application.  Similarly to how you would
> expose an API when communicating with others instead of giving them a
> postgres user account and saying "update these tables" and after a month
> saying "oh, you need to remember to INSERT into this table as well or we
> won't have logs of what you did".
>

for example best practices for PL/SQL by Steven Feuerstein

We can talk about it - it subjective and I know so there are not only one
style.

For example, you can use a ALIAS to parameters if you have a problem with
parameter variables

postgres=# create or replace function foo(out a int) as $$ declare _x alias
for a; begin _x := 10; end $$ language plpgsql;
CREATE FUNCTION
postgres=# select foo();
 foo
-
  10
(1 row)

or if you can be more secure use a block label

postgres=# create or replace function foo(out a int) as $$ <> declare
_x alias for a; begin main._x := 10; end $$ language plpgsql;
CREATE FUNCTION
postgres=# select foo();
 foo
-
  10
(1 row)

The language has these possibilities. Why to add new redundant?

Pavel


>
>
> .marko
>

Re: [HACKERS] PL/pgSQL 1.2

[Marko Tiikkaja]

On 9/4/14 1:47 PM, Pavel Stehule wrote:

2014-09-04 13:37 GMT+02:00 Marko Tiikkaja :

I've started a wiki page with the list of the things I could think of at
this very moment.  I probably got the most annoying ones in there, but I
also might have forgotten about some things.  I invite discussion of every
suggestion on -HACKERS.


where I can wrote comments?


I guess comments could be added as a subsection if you feel -HACKERS 
doesn't work.



I am sorry, It is difficult - your usage of plpgsql is sometimes less, some
times more against best practices :(


Best practices according to whom?  And which parts, exactly?  There's 
quite a lot of stuff in there, both explicitly stated and implicitly 
assumed.



PL functions should not be only envelope to SQL statement


I disagree, to some extent.  Our "external" applications interface with 
the database only by calling functions (which are often written in 
PL/PgSQL).  If that function has no more work to do than to run a single 
query, then yes, the function will just serve as an envelope to a single 
query.  But that also allows the function to be changed in the future 
without having to change the application.  Similarly to how you would 
expose an API when communicating with others instead of giving them a 
postgres user account and saying "update these tables" and after a month 
saying "oh, you need to remember to INSERT into this table as well or we 
won't have logs of what you did".



.marko


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 13:37 GMT+02:00 Marko Tiikkaja :

> Everyone,
>
> I've started a wiki page with the list of the things I could think of at
> this very moment.  I probably got the most annoying ones in there, but I
> also might have forgotten about some things.  I invite discussion of every
> suggestion on -HACKERS.
>
> https://wiki.postgresql.org/wiki/Improving_PL/PgSQL_(September_2014)
>
> I'm sure other people's lists would look different; perhaps they should be
> added to the same page?  Should we divide it based on who's suggesting the
> change to keep a better track?
>
> Anyway, you can start shooting now that we have at least one list of
> concrete proposals.
>

where I can wrote comments?

Usually I wrote them but I have to repeat it.

I am sorry, It is difficult - your usage of plpgsql is sometimes less, some
times more against best practices :(

PL functions should not be only envelope to SQL statement

Pavel


>
>
> .marko
>

Re: [HACKERS] PL/pgSQL 1.2

[Marko Tiikkaja]

Everyone,

I've started a wiki page with the list of the things I could think of at 
this very moment.  I probably got the most annoying ones in there, but I 
also might have forgotten about some things.  I invite discussion of 
every suggestion on -HACKERS.


https://wiki.postgresql.org/wiki/Improving_PL/PgSQL_(September_2014)

I'm sure other people's lists would look different; perhaps they should 
be added to the same page?  Should we divide it based on who's 
suggesting the change to keep a better track?


Anyway, you can start shooting now that we have at least one list of 
concrete proposals.



.marko


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 11:22 GMT+02:00 Joel Jacobson :

> On Thu, Sep 4, 2014 at 11:07 AM, Pavel Stehule 
> 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

Re: [HACKERS] PL/pgSQL 1.2

[Joel Jacobson]

On Thu, Sep 4, 2014 at 11:07 AM, Pavel Stehule  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.

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

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 10:57 GMT+02:00 Joel Jacobson :

> On Thu, Sep 4, 2014 at 10:42 AM, Pavel Stehule 
> wrote:
> > I am strong in opinion so PLpgSQL is targeted primary for implementation
> > business logic in server side. CRUD  is only one from possible use cases
> -
> > and without any special importance to others.
>
> Just curious, what kind of business logic do you write, where "one row"
> is not more special to you than "two rows" or "five rows"?
>
> Think about for a second what PL functions are able to return.
>
> Compare,
> RETURNS RECORD
> vs
> RETURNS SETOF RECORD
>
>
it is different semantic - returns composite or set of composites  --- it
is not row or rows

Actually BL is usually processed oriented, so PL functions coverages
changes in data, and for queries you use SELECT

Returning SET from function is less often - and usually it is not in
preferred patterns because you can very simple block a optimizer.



> When you return from a function, you get exactly 1 row, unless
> you explicitly use the SETOF RECORD syntax, where 0...n rows are possible.
>
> To add to that, we have the STRICT keyword, which also recognize the
> fact 1 row is special.
>
> So, we already *have* special handling for the 1 row case in many areas.
>
> I cannot see how you can fail to agree it would be a good thing to
> make it simple also for UPDATE/DELETE/INSERT.
>
>
>
> On Thu, Sep 4, 2014 at 10:42 AM, Pavel Stehule 
> wrote:
> >
> >
> >
> > 2014-09-04 10:06 GMT+02:00 Joel Jacobson :
> >
> >> On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule 
> >> wrote:
> >> > we have totally different opinion what is good
> >>
> >> Can you elaborate on that?
> >
> >
> > I would to elaborate on enhancing plpgsql - but my primary target is
> > readability without necessity of special special statements, types.
> >
> > I am strong against to create some shortcuts for relative too special use
> > case.
> >
> >>
> >>
> >> Your "ASSERT CHECK ROWCOUNT = 1;" is lengthly, which is why I don't like
> >> it.
> >> Imagine if having to type
> >> my $var === 'foo';
> >> instead of
> >> my $var = 'foo';
> >> on every single line of could where you want to assign a variable,
> >> that would just be ridiculous.
> >>
> >> If you have a typical CRUD application and decide to do *all* data
> >> operations via PL functions,
> >> which is a design pattern advocated by many*, then you will end up
> >> with a lot of very simple
> >> short PL functions, to do things like update_worker_status(),
> >> set_notification_response(), etc,
> >> in which you always pass something which is a primary key in some
> >> table, and want to update
> >> exactly one row. Having to type 27 extra characters for every single
> >> line of code, instead of the
> >> suggested 3 extra characters, is a big difference, for anyone who
> >> designs a CRUD application
> >> which relies on the usage of PL functions.
> >
> >
> > Is not better to design special PL for this usage? I understand to your
> > motivation, but it is not acceptable for me in plpgsql.
> >
> > Ten years ago, we had to solve similar problem - and we designed
> > metalanguage that was translated to plpgsql.
> >
> >>
> >>
> >> For me, it would be useful to understand if you are developing CRUD
> >> applications,
> >> or if your main usage for PL/pgSQL functions are other things?
> >
> >
> > I am strong in opinion so PLpgSQL is targeted primary for implementation
> > business logic in server side. CRUD  is only one from possible use cases
> -
> > and without any special importance to others.
> >
> >>
> >>
> >> If the latter, then maybe that could explain why you don't feel strongly
> >> about
> >> simplifying and condensing the syntax for the most common use-case of
> them
> >> all.
> >
> >
> > I don't agree so what you propose, it is common use case. And I don't
> think
> > so it can be used in synergy with current design
> >
> >>
> >>
> >> *) but there are probably equally who prefer to handle business logics
> >> outside the database
> >
> >
> > It is maybe main difference between me and you. Usually I don't write
> CRUD
> > applications, and I am not sure if plpgsql is good for CRUD.
> >
> > Mainly I would not to optimize plpgsql primary for CRUD.
> >
> >
>

Re: [HACKERS] PL/pgSQL 1.2

[Joel Jacobson]

On Thu, Sep 4, 2014 at 10:42 AM, Pavel Stehule  wrote:
> I am strong in opinion so PLpgSQL is targeted primary for implementation
> business logic in server side. CRUD  is only one from possible use cases -
> and without any special importance to others.

Just curious, what kind of business logic do you write, where "one row"
is not more special to you than "two rows" or "five rows"?

Think about for a second what PL functions are able to return.

Compare,
RETURNS RECORD
vs
RETURNS SETOF RECORD

When you return from a function, you get exactly 1 row, unless
you explicitly use the SETOF RECORD syntax, where 0...n rows are possible.

To add to that, we have the STRICT keyword, which also recognize the
fact 1 row is special.

So, we already *have* special handling for the 1 row case in many areas.

I cannot see how you can fail to agree it would be a good thing to
make it simple also for UPDATE/DELETE/INSERT.



On Thu, Sep 4, 2014 at 10:42 AM, Pavel Stehule  wrote:
>
>
>
> 2014-09-04 10:06 GMT+02:00 Joel Jacobson :
>
>> On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule 
>> wrote:
>> > we have totally different opinion what is good
>>
>> Can you elaborate on that?
>
>
> I would to elaborate on enhancing plpgsql - but my primary target is
> readability without necessity of special special statements, types.
>
> I am strong against to create some shortcuts for relative too special use
> case.
>
>>
>>
>> Your "ASSERT CHECK ROWCOUNT = 1;" is lengthly, which is why I don't like
>> it.
>> Imagine if having to type
>> my $var === 'foo';
>> instead of
>> my $var = 'foo';
>> on every single line of could where you want to assign a variable,
>> that would just be ridiculous.
>>
>> If you have a typical CRUD application and decide to do *all* data
>> operations via PL functions,
>> which is a design pattern advocated by many*, then you will end up
>> with a lot of very simple
>> short PL functions, to do things like update_worker_status(),
>> set_notification_response(), etc,
>> in which you always pass something which is a primary key in some
>> table, and want to update
>> exactly one row. Having to type 27 extra characters for every single
>> line of code, instead of the
>> suggested 3 extra characters, is a big difference, for anyone who
>> designs a CRUD application
>> which relies on the usage of PL functions.
>
>
> Is not better to design special PL for this usage? I understand to your
> motivation, but it is not acceptable for me in plpgsql.
>
> Ten years ago, we had to solve similar problem - and we designed
> metalanguage that was translated to plpgsql.
>
>>
>>
>> For me, it would be useful to understand if you are developing CRUD
>> applications,
>> or if your main usage for PL/pgSQL functions are other things?
>
>
> I am strong in opinion so PLpgSQL is targeted primary for implementation
> business logic in server side. CRUD  is only one from possible use cases -
> and without any special importance to others.
>
>>
>>
>> If the latter, then maybe that could explain why you don't feel strongly
>> about
>> simplifying and condensing the syntax for the most common use-case of them
>> all.
>
>
> I don't agree so what you propose, it is common use case. And I don't think
> so it can be used in synergy with current design
>
>>
>>
>> *) but there are probably equally who prefer to handle business logics
>> outside the database
>
>
> It is maybe main difference between me and you. Usually I don't write CRUD
> applications, and I am not sure if plpgsql is good for CRUD.
>
> Mainly I would not to optimize plpgsql primary for CRUD.
>
>


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 10:53 GMT+02:00 Marko Tiikkaja :

> On 9/4/14 10:42 AM, Pavel Stehule wrote:
>
>> 2014-09-04 10:06 GMT+02:00 Joel Jacobson :
>>
>>> *) but there are probably equally who prefer to handle business logics
>>> outside the database
>>>
>>>  It is maybe main difference between me and you. Usually I don't write
>> CRUD
>> applications, and I am not sure if plpgsql is good for CRUD.
>>
>> Mainly I would not to optimize plpgsql primary for CRUD.
>>
>
> I don't think providing syntax to support the CRUD-like use case would be
> "optimizing it primarily for CRUD".  Changing how UPDATE and DELETE work by
> default would be, but that's not being suggested here (anymore).
>

I am strong in opinion so safe stored procedures should be verbose. It is
in contradiction to Joel direction.

I wrote a proposal, how to do more friendly but still enough verbose

Pavel


>
>
> .marko
>

Re: [HACKERS] PL/pgSQL 1.2

[Marko Tiikkaja]

On 9/4/14 10:42 AM, Pavel Stehule wrote:

2014-09-04 10:06 GMT+02:00 Joel Jacobson :

*) but there are probably equally who prefer to handle business logics
outside the database


It is maybe main difference between me and you. Usually I don't write CRUD
applications, and I am not sure if plpgsql is good for CRUD.

Mainly I would not to optimize plpgsql primary for CRUD.


I don't think providing syntax to support the CRUD-like use case would 
be "optimizing it primarily for CRUD".  Changing how UPDATE and DELETE 
work by default would be, but that's not being suggested here (anymore).



.marko


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 10:06 GMT+02:00 Joel Jacobson :

> On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule 
> wrote:
> > we have totally different opinion what is good
>
> Can you elaborate on that?
>

I would to elaborate on enhancing plpgsql - but my primary target is
readability without necessity of special special statements, types.

I am strong against to create some shortcuts for relative too special use
case.


>
> Your "ASSERT CHECK ROWCOUNT = 1;" is lengthly, which is why I don't like
> it.
> Imagine if having to type
> my $var === 'foo';
> instead of
> my $var = 'foo';
> on every single line of could where you want to assign a variable,
> that would just be ridiculous.
>
> If you have a typical CRUD application and decide to do *all* data
> operations via PL functions,
> which is a design pattern advocated by many*, then you will end up
> with a lot of very simple
> short PL functions, to do things like update_worker_status(),
> set_notification_response(), etc,
> in which you always pass something which is a primary key in some
> table, and want to update
> exactly one row. Having to type 27 extra characters for every single
> line of code, instead of the
> suggested 3 extra characters, is a big difference, for anyone who
> designs a CRUD application
> which relies on the usage of PL functions.
>

Is not better to design special PL for this usage? I understand to your
motivation, but it is not acceptable for me in plpgsql.

Ten years ago, we had to solve similar problem - and we designed
metalanguage that was translated to plpgsql.


>
> For me, it would be useful to understand if you are developing CRUD
> applications,
> or if your main usage for PL/pgSQL functions are other things?
>

I am strong in opinion so PLpgSQL is targeted primary for implementation
business logic in server side. CRUD  is only one from possible use cases -
and without any special importance to others.


>
> If the latter, then maybe that could explain why you don't feel strongly
> about
> simplifying and condensing the syntax for the most common use-case of them
> all.
>

I don't agree so what you propose, it is common use case. And I don't think
so it can be used in synergy with current design


>
> *) but there are probably equally who prefer to handle business logics
> outside the database
>

It is maybe main difference between me and you. Usually I don't write CRUD
applications, and I am not sure if plpgsql is good for CRUD.

Mainly I would not to optimize plpgsql primary for CRUD.

Re: [HACKERS] PL/pgSQL 1.2

[Marko Tiikkaja]

On 9/4/14 2:10 AM, Hannu Krosing wrote:

On 09/04/2014 12:17 AM, Marko Tiikkaja wrote:

I'm not sure how much I like that syntax in cases like:

   WITH t AS (
 -- multi-line query here
   )
   SELECT[0:] foo, bar
   INTO _bat, _man
   FROM foo
   JOIN ..
   JOIN ..
   WHERE ..
   -- etc.

It seems quite well hidden compared to a single keyword at the
beginning of the query.

What do you have in mind ?


I'm just comparing it to the  STRICT UPDATE ..;  and  ONE ROW UPDATE ..; 
 syntaxes proposed earlier.



Is your wiki page already available somewhere ?


I'm working on getting it up ASAP; hopefully it will be today around the 
UTC noon.



.marko


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Joel Jacobson]

On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule  wrote:
> we have totally different opinion what is good

Can you elaborate on that?

Your "ASSERT CHECK ROWCOUNT = 1;" is lengthly, which is why I don't like it.
Imagine if having to type
my $var === 'foo';
instead of
my $var = 'foo';
on every single line of could where you want to assign a variable,
that would just be ridiculous.

If you have a typical CRUD application and decide to do *all* data
operations via PL functions,
which is a design pattern advocated by many*, then you will end up
with a lot of very simple
short PL functions, to do things like update_worker_status(),
set_notification_response(), etc,
in which you always pass something which is a primary key in some
table, and want to update
exactly one row. Having to type 27 extra characters for every single
line of code, instead of the
suggested 3 extra characters, is a big difference, for anyone who
designs a CRUD application
which relies on the usage of PL functions.

For me, it would be useful to understand if you are developing CRUD
applications,
or if your main usage for PL/pgSQL functions are other things?

If the latter, then maybe that could explain why you don't feel strongly about
simplifying and condensing the syntax for the most common use-case of them all.

*) but there are probably equally who prefer to handle business logics
outside the database


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 9:37 GMT+02:00 Joel Jacobson :

> On Wed, Sep 3, 2014 at 11:19 PM, Hannu Krosing 
> wrote:
> > 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
>
> +1 for a new fresh creative idea! My mind was stuck in a "looking for
> keywords" state.
>
> The only suggestion I think is slightly better is the "STRICT UPDATE",
> but only if I'm right assuming the "one row" use-case is much more
> common than the "zero or one rows" and "one or more rows" use-cases.
>
> If all use-cases are equally important to support in a nice way,
> then the suggested syntax is brilliant, as it supports all of them.
>
> Bonus points for the extremely condensed syntax.
>
> > 2. Substitute for EXECUTE with string manipulation
> > 
> >
> > using backticks `` for value/command substitution in SQL as an
> alternative
> > to EXECUTE string
>
> +1 for being backwards compatible, but I have no strong opinion.
>
> > 3. A way to tell pl/pggsql not to cache plans fro normal queries
> >
> ---
>
> > OR we could do it in SQL-ish way using like this:
> >
> > SELECT
> > ...
> > USING FRESH PLAN;
>
> +1 for clean syntax
>

we have totally different opinion what is good

Regards

Pavel

Re: [HACKERS] PL/pgSQL 1.2

[Joel Jacobson]

On Wed, Sep 3, 2014 at 11:19 PM, Hannu Krosing  wrote:
> 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

+1 for a new fresh creative idea! My mind was stuck in a "looking for
keywords" state.

The only suggestion I think is slightly better is the "STRICT UPDATE",
but only if I'm right assuming the "one row" use-case is much more
common than the "zero or one rows" and "one or more rows" use-cases.

If all use-cases are equally important to support in a nice way,
then the suggested syntax is brilliant, as it supports all of them.

Bonus points for the extremely condensed syntax.

> 2. Substitute for EXECUTE with string manipulation
> 
>
> using backticks `` for value/command substitution in SQL as an alternative
> to EXECUTE string

+1 for being backwards compatible, but I have no strong opinion.

> 3. A way to tell pl/pggsql not to cache plans fro normal queries
> ---

> OR we could do it in SQL-ish way using like this:
>
> SELECT
> ...
> USING FRESH PLAN;

+1 for clean syntax


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-03 23:19 GMT+02:00 Hannu Krosing :

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

Re: [HACKERS] PL/pgSQL 1.2

[Hannu Krosing]

On 09/04/2014 12:17 AM, Marko Tiikkaja wrote:
> On 2014-09-03 23:19, Hannu Krosing wrote:
>> 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
>
> I'm not sure how much I like that syntax in cases like:
>
>   WITH t AS (
> -- multi-line query here
>   )
>   SELECT[0:] foo, bar
>   INTO _bat, _man
>   FROM foo
>   JOIN ..
>   JOIN ..
>   WHERE ..
>   -- etc.
>
> It seems quite well hidden compared to a single keyword at the
> beginning of the query.
What do you have in mind ?

Is your wiki page already available somewhere ?
>
> It's also not clear whether all of this flexibility is required.
> Enforcing "exactly one" conveniently is my main priority.
What do you want here on top of SELECT ... INTO STRICT ... ?
> Supporting the "at most one" case could be nice, too, but anything
> else feels like overkill.  Though if the syntax is based on numbers
> (and not a keyword), then I guess we get the flexibility for free anyway.
>
> I also have my doubts about how easy it would be to implement this
> syntax given that we're using the "real" SQL parser.
Definitely not trivial, but at least doable :)

Finding and processing SELECT[...] could probably even be done with
a (regex-based ?) pre-parser .


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



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Marko Tiikkaja]

On 2014-09-03 23:19, Hannu Krosing wrote:

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


I'm not sure how much I like that syntax in cases like:

  WITH t AS (
-- multi-line query here
  )
  SELECT[0:] foo, bar
  INTO _bat, _man
  FROM foo
  JOIN ..
  JOIN ..
  WHERE ..
  -- etc.

It seems quite well hidden compared to a single keyword at the beginning 
of the query.


It's also not clear whether all of this flexibility is required. 
Enforcing "exactly one" conveniently is my main priority.  Supporting 
the "at most one" case could be nice, too, but anything else feels like 
overkill.  Though if the syntax is based on numbers (and not a keyword), 
then I guess we get the flexibility for free anyway.


I also have my doubts about how easy it would be to implement this 
syntax given that we're using the "real" SQL parser.



.marko


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Kevin Grittner]

Hannu Krosing  wrote:

> [suggested syntax]

Interesting.  The only one that really offends me is:

> SELECT * FROM `tablename` WHERE "`idcolumn`" = idvalue;

I think that should be:

SELECT * FROM `tablename` WHERE `"idcolumn"` = idvalue;

i.e., I think the backticks belong on the outside.


--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 1.2

[Hannu Krosing]

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Ü



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers