Thread: 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