Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread 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;

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread Joel Jacobson
On Sat, Sep 6, 2014 at 6:59 AM, Pavel Stehule pavel.steh...@gmail.com 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;

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread Pavel Stehule
2014-09-06 15:12 GMT+02:00 Joel Jacobson j...@trustly.com: On Sat, Sep 6, 2014 at 6:59 AM, Pavel Stehule pavel.steh...@gmail.com wrote: People can prepare a simple functions like you did: ... CREATE OR REPLACE FUNCTION user_list () RETURNS SETOF id AS $$ BEGIN RETURN QUERY

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread 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.

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-05 Thread 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-05 Thread Pavel Stehule
2014-09-06 4:25 GMT+02:00 Marko Tiikkaja ma...@joh.to: 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On Wed, Sep 3, 2014 at 11:19 PM, Hannu Krosing ha...@2ndquadrant.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Pavel Stehule
2014-09-04 9:37 GMT+02:00 Joel Jacobson j...@trustly.com: On Wed, Sep 3, 2014 at 11:19 PM, Hannu Krosing ha...@2ndquadrant.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule pavel.steh...@gmail.com 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';

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Pavel Stehule
2014-09-04 10:06 GMT+02:00 Joel Jacobson j...@trustly.com: On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule pavel.steh...@gmail.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Marko Tiikkaja
On 9/4/14 10:42 AM, Pavel Stehule wrote: 2014-09-04 10:06 GMT+02:00 Joel Jacobson j...@trustly.com: *) 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On Thu, Sep 4, 2014 at 10:42 AM, Pavel Stehule pavel.steh...@gmail.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Pavel Stehule
2014-09-04 10:53 GMT+02:00 Marko Tiikkaja ma...@joh.to: On 9/4/14 10:42 AM, Pavel Stehule wrote: 2014-09-04 10:06 GMT+02:00 Joel Jacobson j...@trustly.com: *) but there are probably equally who prefer to handle business logics outside the database It is maybe main difference between me

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Pavel Stehule
2014-09-04 10:57 GMT+02:00 Joel Jacobson j...@trustly.com: On Thu, Sep 4, 2014 at 10:42 AM, Pavel Stehule pavel.steh...@gmail.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On Thu, Sep 4, 2014 at 11:07 AM, Pavel Stehule pavel.steh...@gmail.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. Actually BL is usually processed oriented, so PL

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Pavel Stehule
2014-09-04 11:22 GMT+02:00 Joel Jacobson j...@trustly.com: On Thu, Sep 4, 2014 at 11:07 AM, Pavel Stehule pavel.steh...@gmail.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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread 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.

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Pavel Stehule
2014-09-04 13:37 GMT+02:00 Marko Tiikkaja ma...@joh.to: 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Marko Tiikkaja
On 9/4/14 1:47 PM, Pavel Stehule wrote: 2014-09-04 13:37 GMT+02:00 Marko Tiikkaja ma...@joh.to: 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Pavel Stehule
2014-09-04 13:54 GMT+02:00 Marko Tiikkaja ma...@joh.to: On 9/4/14 1:47 PM, Pavel Stehule wrote: 2014-09-04 13:37 GMT+02:00 Marko Tiikkaja ma...@joh.to: 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Robert Haas
On Thu, Sep 4, 2014 at 4:06 AM, Joel Jacobson j...@trustly.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On 4 sep 2014, at 11:42, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 11:22 GMT+02:00 Joel Jacobson j...@trustly.com: 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Pavel Stehule
2014-09-04 14:37 GMT+02:00 Joel Jacobson j...@trustly.com: On 4 sep 2014, at 11:42, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 11:22 GMT+02:00 Joel Jacobson j...@trustly.com: The point was, RETURNS returns 1 while RETURNS SETOF returns 0 .. n. no RETURNS return VALUE (it is

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Jan Wieck
On 09/04/2014 01:14 AM, Pavel Stehule wrote: 2014-09-03 23:19 GMT+02:00 Hannu Krosing ha...@2ndquadrant.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Pavel Stehule
2014-09-04 15:24 GMT+02:00 Jan Wieck j...@wi3ck.info: On 09/04/2014 01:14 AM, Pavel Stehule wrote: 2014-09-03 23:19 GMT+02:00 Hannu Krosing ha...@2ndquadrant.com A more SQL-ish way of doing the same could probably be called COMMAND CONSTRAINTS and look something like this

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Jan Wieck
On 09/04/2014 09:31 AM, Pavel Stehule wrote: 2014-09-04 15:24 GMT+02:00 Jan Wieck j...@wi3ck.info 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,

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Pavel Stehule
2014-09-04 15:38 GMT+02:00 Jan Wieck j...@wi3ck.info: On 09/04/2014 09:31 AM, Pavel Stehule wrote: 2014-09-04 15:24 GMT+02:00 Jan Wieck j...@wi3ck.info 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread 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.

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Hannu Krosing
On 09/04/2014 02:40 PM, Pavel Stehule wrote: 2014-09-04 14:37 GMT+02:00 Joel Jacobson j...@trustly.com mailto:j...@trustly.com: On 4 sep 2014, at 11:42, Pavel Stehule pavel.steh...@gmail.com mailto:pavel.steh...@gmail.com wrote: 2014-09-04 11:22 GMT+02:00 Joel Jacobson

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On 4 sep 2014, at 15:32, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 15:24 GMT+02:00 Jan Wieck j...@wi3ck.info: On 09/04/2014 01:14 AM, Pavel Stehule wrote: 2014-09-03 23:19 GMT+02:00 Hannu Krosing ha...@2ndquadrant.com A more SQL-ish way of doing the same could probably

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On 4 sep 2014, at 16:45, Hannu Krosing ha...@2ndquadrant.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Pavel Stehule
2014-09-04 17:16 GMT+02:00 Joel Jacobson j...@trustly.com: On 4 sep 2014, at 16:45, Hannu Krosing ha...@2ndquadrant.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Pavel Stehule
2014-09-04 17:10 GMT+02:00 Joel Jacobson j...@trustly.com: On 4 sep 2014, at 15:32, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 15:24 GMT+02:00 Jan Wieck j...@wi3ck.info: On 09/04/2014 01:14 AM, Pavel Stehule wrote: 2014-09-03 23:19 GMT+02:00 Hannu Krosing

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On 4 sep 2014, at 17:18, Pavel Stehule pavel.steh...@gmail.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Jan Wieck
On 09/04/2014 11:16 AM, Joel Jacobson wrote: On 4 sep 2014, at 16:45, Hannu Krosing ha...@2ndquadrant.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Robert Haas
On Thu, Sep 4, 2014 at 11:32 AM, Joel Jacobson j...@trustly.com wrote: On 4 sep 2014, at 17:18, Pavel Stehule pavel.steh...@gmail.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Kevin Grittner
Pavel Stehule pavel.steh...@gmail.com 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:

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Pavel Stehule
2014-09-04 18:02 GMT+02:00 Kevin Grittner kgri...@ymail.com: Pavel Stehule pavel.steh...@gmail.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On Thu, Sep 4, 2014 at 5:51 PM, Robert Haas robertmh...@gmail.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-03 Thread 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-03 Thread Kevin Grittner
Hannu Krosing ha...@2ndquadrant.com 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-03 Thread 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-03 Thread 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-03 Thread Pavel Stehule
2014-09-03 23:19 GMT+02:00 Hannu Krosing ha...@2ndquadrant.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