Thread: rules over multiple tables

Hi all,

I'm new to Postgresql (using Postgresql 7.1.3) and I want to create rules.

I have a Database which has two tables (Addresses and Contacts) and I
created views (like in the documentation, it works), and I want to create rules for
insert, update and delete.

The syntax for the insert-rule I found (and it works), but nothing about how
to create update and delete rules over two tables. In the documentation, I
only found examples how to create rules over one table (this works, but I want
it over two tables).

The syntax I used for insert in two tables:

create rule view_test_insert as on insert to view_test do instead (
insert into "ADDRESSES" ("ID_ADDR" , "COMPANY" , etc...)
values (new."ID_ADDR" , new."COMPANY" , etc...);
insert into "CONTACTS" ("ID" , "C_CATEGORY" , etc...)
values (new."ID" , new."C_CATEGORY" , etc...);
);

I tried update and delete whit that syntax but it dosn't work ERROR:
parser: parse error at or near "(".

Does anyone know the solution for my problem?

Thank you!

--
GMX - Die Kommunikationsplattform im Internet.
http://www.gmx.net

Daniel,

> I'm new to Postgresql (using Postgresql 7.1.3) and I want to create
>  rules.

Upgrade to 7.2 when you can.  The improvements are worth it.

> I tried update and delete whit that syntax but it dosn't work ERROR:
> parser: parse error at or near "(".
>
> Does anyone know the solution for my problem?

Please post full schema for the problem (i.e. both table definitions,
 the view definition, and the rules).  This should work; likely it's
 just a punctuation problem.

-Josh Berkus

Hi all,

I'm now upgrading to Version 7.2.

Here's already the Syntax that dosn't work:

create function
delete_irgendwas_function

(int4,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,text,text,text,text,text,text,text,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,int4,varchar,date,time,varchar,varchar,text,varchar,varchar,int4,varchar,int4,varchar,varchar,varchar,varchar,varchar,varchar,varchar,date,varchar)
returns boolean as '
declare
"ID_ADDR" alias for $1;
"COMPANY" alias for $2;
...
"SIGN" alias for $65;
begin
delete from "ADDRESSES"
where "ID_ADDR" = f_"ID_ADDR" AND "COMPANY" = f_"COMPANY" ...;
delete from "CONTACTS"
where "ID" = f_"ID" AND "C_CATEGORY" = f_"C_CATEGORY" ...;
return 1;
END;'
LANGUAGE 'internal';
ERROR:  Procedures cannot take more than 16 arguments

I tried to change MAX_ARGS in /src/include/config.h.in but it dosn't work
(compilation failed, unknown attribute).

Has anyone another Idea how to delete (and update) over two tables?

--
GMX - Die Kommunikationsplattform im Internet.
http://www.gmx.net

Daniel Grob <dgrob@gmx.net> writes:
> I tried to change MAX_ARGS in /src/include/config.h.in but it dosn't work

Lots of other people do that without any problems.

            regards, tom lane

Hi all,

I've now updated to Postgres 7.2 and increased the max. Arguments to 70.

With the same syntax I used before (and get the Error that there are too
many arguments) I now get another Error:

ERROR:  there is no built-in function named "
declare
"ID_ADDR" alias for $1;
...
return 1;
END;"


See below the Syntax I used:

create function
delete_irgendwas_function (int4,varchar,...)
returns boolean as '
declare
"ID_ADDR" alias for $1;
"COMPANY" alias for $2;
"SIGN" alias for $65;
begin
delete from "ADDRESSES"
where "ID_ADDR" = f_"ID_ADDR" AND "COMPANY" = f_"COMPANY"...;
delete from "CONTACTS"
where "ID" = f_"ID" AND "C_CATEGORY" = f_"C_CATEGORY" AND .... "SIGN" =
f_"SIGN";
return 1;
END;'
LANGUAGE 'internal';


Has anyone an idea what I maybe have do wrong?

Thanks for help.



--
GMX - Die Kommunikationsplattform im Internet.
http://www.gmx.net

Hi,
<snip>
>
> ERROR:  there is no built-in function named "
> declare
> "ID_ADDR" alias for $1;
> ...
> return 1;
> END;"
>
>
> See below the Syntax I used:
>
> create function
> delete_irgendwas_function (int4,varchar,...)
> returns boolean as '
> declare
> "ID_ADDR" alias for $1;
> "COMPANY" alias for $2;
> "SIGN" alias for $65;
I personally would try and avoid variables with quotes round them (not sure
if its even legal). Instead I'd do something like
f_id_addr alias for $1;

> begin
> delete from "ADDRESSES"
> where "ID_ADDR" = f_"ID_ADDR" AND "COMPANY" = f_"COMPANY"...;
Having variables named the same as fields can get confusing (for the parser
as well as people).
See above for how I personally would name

> delete from "CONTACTS"
> where "ID" = f_"ID" AND "C_CATEGORY" = f_"C_CATEGORY" AND
> .... "SIGN" =
> f_"SIGN";
> return 1;
> END;'
> LANGUAGE 'internal';
Should be LANGUAGE 'plpgsql';
Hope this helps,
- Stuart