Thread: Rules: passing new/old to functions
How to pass the special variables new / old to functions? e.g. I'd like to to something like this (v is a view): create funtion f1(v, v) returns void as '...' create rule vupt as on update to v do instead select f1(new, old) While accepting this definitions, pg says it can not handle "whole-tuple references" at runtime. using: select f(new) from new doesn't work either: pg says relation new doesn't exist. Since I need to handle tables with more than 20 columns, it would be uggly if I had to pass every single value to the function as follows: select f1(new.1, ..., new.n, old.1, ..., old.n) create function f1(typeof 1, ..., typeof n, typeof 1, ..., typeofn) I'd be glad for any kind of suggestion. regards,Gunter -- +++ GMX - Mail, Messaging & more http://www.gmx.net +++ Bitte lächeln! Fotogalerie online mit GMX ohne eigene Homepage!
Gunter Diehl <gdid@gmx.de> writes:
> create funtion f1(v, v) returns void as '...'
> create rule vupt as on update to v do instead select f1(new, old)
> While accepting this definitions, pg says it can not handle "whole-tuple
> references" at runtime.
FWIW, the "old" case works fine. "new" doesn't work so well because the
new row hasn't been formed into a tuple at the point where the rule
runs; it only exists as a list of variables. (This is a rather handwavy
explanation, but I think it captures the key point.) While this could
probably be fixed with some effort, I doubt it's going to happen soon.
Is there any chance of doing what you want with a trigger instead of a
rule?
regards, tom lane
Hi,
I have a product table identified by its id field. There is a productgroups
table with productisd, productgroupid fields. And I have a prod_in_pgr
(productid, productgroupid) table which describes the membership of
productgroups. Each product can be a member of zero or more productgroups,
but one productgroup can contain a product only once.
I would like to list the following information: productgroupid | productid | ... some other prouduct info | ...
I need all the products even if it is not a member in any productgroups. I
need these information ordered by productgroup and then productid.
--------------------------------
An example:
select t_productgroups.name as pgroup, t_products.id as productid
from t_products
join t_prod_in_pgr on (t_products.id=productid)
join t_productgroups on (t_productgroups.id = t_prod_in_pgr.productgroupid)
order by pgroup, productid;
-------------------->
pgroup | productid
----------------------+-----------
Alumínium profilok | 6047
Alumínium profilok | 6048
Alumínium profilok | 6049
Alumínium profilok | 6050
Alumínium profilok | 6051
Alumínium profilok | 6052
Alumínium profilok | 6053
Alumínium profilok | 6054
Alumínium profilok | 6055
Alumínium profilok | 6056
Alumínium profilok | 6057
Alumínium profilok | 6058
Alumínium profilok | 6059
Alumínium profilok | 6060
Alumínium profilok | 6061
Alumínium profilok | 6062
Gumik | 6063
Hohíd mentes profilok | 6060
Hohíd mentes profilok | 6061
Hohíd mentes profilok | 6062
Hohidas profilok | 6050
Hohidas profilok | 6051
Hohidas profilok | 6052
Hohidas profilok | 6053
Hohidas profilok | 6054
Hohidas profilok | 6055
Hohidas profilok | 6056
Hohidas profilok | 6057
Hohidas profilok | 6058
Hohidas profilok | 6059
Nyílászárók | 6064
--------------------------------------------------------
I hope it is understandable. This query is a result of a 'join'-ed query
(see above), but it can contain only those products which are in one or more
groups. But I also need the ungroupd items.
Pleas tell me how to create such an sql query.
Thank you,
-- Csaba
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
On Fri, Jun 27, 2003 at 13:13:07 +0200, Együd Csaba <csegyud@freemail.hu> wrote: > Hi, Please don't reply to messages to start a new thread. > I have a product table identified by its id field. There is a productgroups > table with productisd, productgroupid fields. And I have a prod_in_pgr > (productid, productgroupid) table which describes the membership of > productgroups. Each product can be a member of zero or more productgroups, > but one productgroup can contain a product only once. > > I would like to list the following information: > productgroupid | productid | ... some other prouduct info | ... > > I need all the products even if it is not a member in any productgroups. I > need these information ordered by productgroup and then productid. > -------------------------------- > An example: > > select t_productgroups.name as pgroup, > t_products.id as productid > from t_products > join t_prod_in_pgr on (t_products.id=productid) > join t_productgroups on (t_productgroups.id = t_prod_in_pgr.productgroupid) > order by pgroup, productid; I think you want something like: select t_productgroups.name as pgroup, t_products.id as productid from t_products left join (t_prod_in_pgr join t_productgroups on (t_productgroups.id = t_prod_in_pgr.productgroupid)) on (t_products.id=productid) order by pgroup, productid;
Bruno, > Please don't reply to messages to start a new thread. sorry, I will never do such things in the future. > select t_productgroups.name as pgroup, > t_products.id as productid > from t_products > left join (t_prod_in_pgr > join t_productgroups on (t_productgroups.id = t_prod_in_pgr.productgroupid)) > on (t_products.id=productid) > order by pgroup, productid; This is absolutelly what I want, but I can't understand how it is working. Where can I find a descriptive (tale-like, for kids ... :) ) documentation about using joins? Thank you wery mauch. -- Csaba --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
On Fri, Jun 27, 2003 at 16:18:10 +0200, Együd Csaba <csegyud@freemail.hu> wrote: > > This is absolutelly what I want, but I can't understand how it is working. > Where can I find a descriptive (tale-like, for kids ... :) ) documentation > about using joins? If you look at the documentation for the select command and page down a bit there is a description of join syntax. Note that in 7.4 using the explicit join syntax won't force join order. (This really only affects cross joins and inner joins; left and right joins normally can't be reordered.) You need a left join to pick up products that aren't in any group. The parenthesis changed the join order so that group names were attached to group ids before group ids were joined to products. This can have performance implications. I think that this is probably the faster way, but the other option would to have been to make the second join a left join as well.
Dear All, thank you for your help, it was really efficient. I'll get by with it now. Thanks. -- Csaba ----- Original Message ----- From: "Bruno Wolff III" <bruno@wolff.to> To: "Együd Csaba" <csegyud@freemail.hu> Cc: <pgsql-sql@postgresql.org> Sent: Friday, June 27, 2003 5:10 PM Subject: Re: [SQL] Getting all rows even if not a member of any groups > On Fri, Jun 27, 2003 at 16:18:10 +0200, > Együd Csaba <csegyud@freemail.hu> wrote: > > > > This is absolutelly what I want, but I can't understand how it is working. > > Where can I find a descriptive (tale-like, for kids ... :) ) documentation > > about using joins? > > If you look at the documentation for the select command and page down a > bit there is a description of join syntax. Note that in 7.4 using > the explicit join syntax won't force join order. (This really only > affects cross joins and inner joins; left and right joins normally > can't be reordered.) > > You need a left join to pick up products that aren't in any group. > The parenthesis changed the join order so that group names were attached > to group ids before group ids were joined to products. This can have > performance implications. I think that this is probably the faster > way, but the other option would to have been to make the second join > a left join as well. > > > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.