Postgres Pro
Downloads
Home   >   mailing lists

Thread: Rules and conditions

Rules and conditions

From
George Silva
Date:
Hello guys,

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?):

Take a look:

CREATE OR REPLACE RULE instead_update AS ON UPDATE TO foo
DO INSTEAD
(
    IF exists(SELECT 1 FROM versioning.foo_version_1 WHERE oid = new.oid)
= 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;
    ELSE
        INSERT INTO versioning.foo_version_1 VALUES (NEW.*,'UPDATE');
    END IF;
)

Any toughts?

Thanks
--
George R. C. Silva

Desenvolvimento em GIS
www.sextantegeo2.blogspot.com

Re: Rules and conditions

From
Guillaume Lelarge
Date:
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

Re: Rules and conditions

From
Leif Biberg Kristensen
Date:
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>

Re: Rules and conditions

From
George Silva
Date:
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>
>>
>> --
>> 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

Re: Rules and conditions

From
Guillaume Lelarge
Date:
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