Rules and conditions

Le mercredi 9 décembre 2009 à 01:52:03, George Silva a écrit :

[...]
I can't seem to understand why a simples if is not working on the
creation of rules.

I tried both ways (am i missing something?):

Sure. IF does not exist in SQL, and you can only put SQL statements in a rule.
Instead, you can call a stored function written in PL/pgsql which supports IF
structure.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

On Wednesday 9. December 2009 09.06.12 Guillaume Lelarge wrote:

Le mercredi 9 décembre 2009 à 01:52:03, George Silva a écrit :

[...]
I can't seem to understand why a simples if is not working on the
creation of rules.

I tried both ways (am i missing something?):

Sure. IF does not exist in SQL, and you can only put SQL statements in a

rule.

Instead, you can call a stored function written in PL/pgsql which supports

IF

structure.

Or you can look up the CASE WHEN ... THEN ... ELSE .. END, construct, which
works very much like IF / THEN / ELSE:
<http://www.postgresql.org/docs/current/static/functions-conditional.html&gt;

Hello guys,

Still having some trouble with this.

I'm trying to use a case when, but postgres is still giving me syntax error.

CREATE OR REPLACE RULE instead_update AS ON UPDATE TO foo
DO INSTEAD
(
CASE SELECT exists(SELECT 1 FROM versioning.foo_version_1 WHERE
OLD.oid = NEW.oid) as a WHEN a=true THEN
UPDATE versioning.foo_version_1 SET
oid = new.oid,
att1 = new.att1,
att2 = new.att2,
the_geom = new.the_geom,
status = 'UPDATE'
WHERE oid = new.oid;
WHEN a=false THEN
INSERT INTO versioning.foo_version_1(NEW.*,'UPDATE');
END
);

Any thoughts?

Thanks

On Wed, Dec 9, 2009 at 8:56 AM, George Silva <georger.silva@gmail.com> wrote:

Thanks guys for clearing my head.

George

On Wed, Dec 9, 2009 at 7:07 AM, Leif Biberg Kristensen
<leif@solumslekt.org> wrote:

On Wednesday 9. December 2009 09.06.12 Guillaume Lelarge wrote:

Le mercredi 9 décembre 2009 à 01:52:03, George Silva a écrit :

[...]
I can't seem to understand why a simples if is not working on the
creation of rules.

I tried both ways (am i missing something?):

Sure. IF does not exist in SQL, and you can only put SQL statements in a

rule.

Instead, you can call a stored function written in PL/pgsql which supports

IF

structure.

Or you can look up the CASE WHEN ... THEN ... ELSE .. END, construct, which
works very much like IF / THEN / ELSE:
<http://www.postgresql.org/docs/current/static/functions-conditional.html&gt;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
George R. C. Silva

Desenvolvimento em GIS
www.sextantegeo2.blogspot.com

--
George R. C. Silva

Desenvolvimento em GIS
www.sextantegeo2.blogspot.com

Le mercredi 9 décembre 2009 à 12:38:33, George Silva a écrit :

Hello guys,

Still having some trouble with this.

I'm trying to use a case when, but postgres is still giving me syntax
error.

CREATE OR REPLACE RULE instead_update AS ON UPDATE TO foo
DO INSTEAD
(
CASE SELECT exists(SELECT 1 FROM versioning.foo_version_1 WHERE
OLD.oid = NEW.oid) as a WHEN a=true THEN
UPDATE versioning.foo_version_1 SET
oid = new.oid,
att1 = new.att1,
att2 = new.att2,
the_geom = new.the_geom,
status = 'UPDATE'
WHERE oid = new.oid;
WHEN a=false THEN
INSERT INTO versioning.foo_version_1(NEW.*,'UPDATE');
END
);

Any thoughts?

You can't use the CASE structure this way. You really need to use a stored
function.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com