rules
This may be a bit of stupid question but it is the first time i have
played around with rules and i am trying to convert a sql statement into
a rule
sample sql querty
INSERT INTO schema.table2
(
"field1",
"field2",
"field3",
"field4",
"field5",
"field6",
"field7",
"field8",
"field9",
)
VALUES
(
SELECT
table1."name",
table1.notes,
table1.hrs,
table1.days,
table3.value,
table31.value,
table4.ratename,
table4.maxhrs,
table4.appliesafter,
table4.rate,
table5.value,
table5."name"
FROM
(inner joins)
Where
primary = number
)
which i would like to make into a rule if possible
Hi,
Citing Jamie Deppeler <jamie@doitonce.net.au>:
sample sql querty
INSERT INTO schema.table2
(
"field1",
"field2",
"field3",
"field4",
"field5",
"field6",
"field7",
"field8",
"field9",
)
VALUES
(
SELECT
table1."name",
table1.notes,
table1.hrs,
table1.days,
table3.value,
table31.value,
table4.ratename,
table4.maxhrs,
table4.appliesafter,
table4.rate,
table5.value,
table5."name"
FROM
(inner joins)
Where
primary = number
)which i would like to make into a rule if possible
First of all, you should fix your query. Problems I see:
- You're inserting more values than you specified fields.
- On postgresql you don't have to use the
insert into foo (...) values (select ...)
construct, you can instead say:
insert into foo (...) select ...
For the rule stuff:
- do you want it to happen on update, on delete or on insert? You should
be clear about that.
You might want to read Bruce Momjian's book "PostgreSQL - Introduction
and Concepts" (available in your book store or on the web - I leave it
up to you to google it up or to order it). It's a really fine book for
beginners and helped me a lot to learn how to write rules and do other
stuff with postgresql.
Regards,
Daniel