Thread: Transactions and functions
Transactions and functions
From
"
Date:
I have read (in the manual? Can't remember ...) that you cannot BEGIN a transaction in the body of a CREATE FUNCTION (at least for pltcl functions - I seem to remember it has something to do with the SPI interface, so that I assume it is true for any other user defined function). As I want to do some work within such a function, and I do need the transaction to be protected from concurrent access by other users, I am wondering how to do this. Specifically, the question is: does a single SQL statement of the form SELECT myFunction() constitute a transaction, even if it involves multiple SQL statements and SPI calls within the function definition? Assuming the answer to the previous question is 'yes': how can I insure that the transaction isolation level is set to SERIALIZABLE? Do I have to do it from the calling application, or is there a way to let the function itself set the required isolation level? Thanks Miguel Sofer
hi, can having clause have a sub select? here is a example that I abstracted and I played with. it is the "classic" example: offices, salesreps, products, customers, orders we just use salesreps and orders: --------------------------------------------------------- create table salesreps (empl_num integer not null,name varchar (15) not null,age integer,rep_office integer,title varchar (10),hiredate date not null,manager integer,quota money,sales money notnull ); create table orders (order_num integer not null,order_date date not null,cust integer not null,rep integer,mfr char(3) not null,product char(5) not null,qty integer not null,amount money not null ); select rep, sum(amount) from orders group by rep having sum(amount) < (select quota from salesreps where empl_num = rep); ------------------------------------------------------------- the ideas is to get the sales amount of sales-representives that sales less than his/her quota. for the query, I got (I'm using 6.5.1): ERROR: Illegal use of aggregates or non-group column in HAVING clause thanks!!!
<kaiq@realtyideas.com> writes: > select rep, sum(amount) > from orders > group by rep > having sum(amount) < (select quota from salesreps where empl_num = rep); > for the query, I got (I'm using 6.5.1): > ERROR: Illegal use of aggregates or non-group column in HAVING clause Seems to work OK in 6.5.3 and in current development sources ... I'm not quite sure why it fails in 6.5.1 and not in 6.5.3, because I can't find any indication that parse_aggs.c changed in between. But please update and see if you still have the problem. regards, tom lane
sorry, I messed up. Actually the query works. but how can I put the quota within the select? is it possible? select rep, sum(amount), quota from orders, salesreps where empl_num = rep group by rep having sum(amount) < (select quota from salesreps where empl_num = rep); the error: ERROR: Illegal use of aggregates or non-group column in target list I know that for one table, with "group", select can only have the grouping non-aggregate attributes, and it makes sense. However, for two tables, the constraint does not make sense--i.e., it is required in many situations. Here, every sales-representative has and only has one quota. so, it is quite reasonable to select both. Is there any work arounds except making two queries? thanks On Wed, 15 Dec 1999 kaiq@realtyideas.com wrote: > hi, can having clause have a sub select? > here is a example that I abstracted and I played with. > it is the "classic" example: > offices, salesreps, products, customers, orders > we just use salesreps and orders: > --------------------------------------------------------- > create table salesreps ( > empl_num integer not null, > name varchar (15) not null, > age integer, > rep_office integer, > title varchar (10), > hiredate date not null, > manager integer, > quota money, > sales money not null > ); > > create table orders ( > order_num integer not null, > order_date date not null, > cust integer not null, > rep integer, > mfr char(3) not null, > product char(5) not null, > qty integer not null, > amount money not null > ); > > select rep, sum(amount) > from orders > group by rep > having sum(amount) < (select quota from salesreps where empl_num = rep); > ------------------------------------------------------------- > the ideas is to get the sales amount of sales-representives that > sales less than his/her quota. > > for the query, I got (I'm using 6.5.1): > ERROR: Illegal use of aggregates or non-group column in HAVING clause > > > > thanks!!! > > > > > > > ************ >
<kaiq@realtyideas.com> writes: > select rep, sum(amount), quota > from orders, salesreps > where empl_num = rep > group by rep > having sum(amount) < (select quota from salesreps where empl_num = rep); > ERROR: Illegal use of aggregates or non-group column in target list > I know that for one table, with "group", select can only have the grouping > non-aggregate attributes, and it makes sense. However, for two tables, > the constraint does not make sense--i.e., it is required in many > situations. Here, every sales-representative has and only has one > quota. so, it is quite reasonable to select both. Well, the brute-force way is to group by both columns, which also lets you get rid of the subselect: select rep, sum(amount), quota from orders, salesreps where empl_num = rep group by rep, quota having sum(amount) < quota; which will give you only one output row per rep if the reps do only have one quota. Another way, which depends even more on the assumption that there's only one quota value per rep, is to use a "dummy" aggregate: select rep, sum(amount), min(quota) from orders, salesreps where empl_num = rep group by rep having sum(amount) < min(quota); (max(quota) would work just as well, of course, or even sum or avg). But both of these feel like unsatisfying kluges. Any SQL experts know the conceptually "right" way to do this? regards, tom lane