Re: [HACKERS] PL/pgSQL 1.2

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

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

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 i

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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 REPLAC

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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 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'v

Re: [HACKERS] PL/pgSQL 1.2

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 do

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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 Enter

Re: [HACKERS] PL/pgSQL 1.2

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,

Re: [HACKERS] PL/pgSQL 1.2

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 th

Re: [HACKERS] PL/pgSQL 1.2

> 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

Re: [HACKERS] PL/pgSQL 1.2

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 proba

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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 some

Re: [HACKERS] PL/pgSQL 1.2

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 >

Re: [HACKERS] PL/pgSQL 1.2

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 oppos

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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 b

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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 a

Re: [HACKERS] PL/pgSQL 1.2

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 va

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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/Impr

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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 chang

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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 d

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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 sp

Re: [HACKERS] PL/pgSQL 1.2

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

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 =

Re: [HACKERS] PL/pgSQL 1.2

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 S

Re: [HACKERS] PL/pgSQL 1.2

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 b

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

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 th

Re: [HACKERS] PL/pgSQL 1.2

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

Re: [HACKERS] PL/pgSQL 1.2

On 09/03/2014 05:09 PM, Marko Tiikkaja wrote: > On 9/3/14 5:05 PM, Bruce Momjian wrote: >> On Wed, Sep 3, 2014 at 07:54:09AM +0200, Pavel Stehule wrote: >>> I am not against to improve a PL/pgSQL. And I repeat, what can be >>> done and can >>> be done early: >>> >>> a) ASSERT clause -- with some o