Обсуждение: Transactions and functions

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