Thread: PL/pgSQL examples NOT involving functions

How can I write a few lines of PL/pgSQL which do not involve creating
a function?  I can find no examples of this in the docs, but say I
would like to do something like

    BEGIN
    IF EXISTS (SELECT * FROM foo WHERE idx = 27)
    THEN
        UPDATE foo SET var='some value' WHERE idx=27;
    ELSE
        INSERT INTO foo (idx, var) VALUES (27, 'some value');
    END IF
    END;

roland
--
               PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                     76-15 113th Street, Apt 3B
roland@astrofoto.org                       Forest Hills, NY 11375

Well, to the best of my knowledge your question is a contradiction in terms:

plpgsql is a procedural language to use it you must create a stored procedure or function...

However you are probably able to do many of the things you may want with plain old SQL look it up in the docs
especiallythe case structure. 

Regards,

Aasmund.
On 15 Nov 2001 11:49:12 -0500, Roland Roberts <roland@astrofoto.org> wrote:
> How can I write a few lines of PL/pgSQL which do not involve creating
> a function?  I can find no examples of this in the docs, but say I
> would like to do something like
>
>     BEGIN
>     IF EXISTS (SELECT * FROM foo WHERE idx = 27)
>     THEN
>         UPDATE foo SET var='some value' WHERE idx=27;
>     ELSE
>         INSERT INTO foo (idx, var) VALUES (27, 'some value');
>     END IF
>     END;
>
> roland
> --
>                PGP Key ID: 66 BC 3B CD
> Roland B. Roberts, PhD                             RL Enterprises
> roland@rlenter.com                     76-15 113th Street, Apt 3B
> roland@astrofoto.org                       Forest Hills, NY 11375
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46

> > How can I write a few lines of PL/pgSQL which do not involve creating
> > a function?  I can find no examples of this in the docs, but say I
> > would like to do something like
> >
> >     BEGIN
> >     IF EXISTS (SELECT * FROM foo WHERE idx = 27)
> >     THEN
> >         UPDATE foo SET var='some value' WHERE idx=27;
> >     ELSE
> >         INSERT INTO foo (idx, var) VALUES (27, 'some value');
> >     END IF
> >     END;

Interesting.  I see you are trying to simulate MySQL's 'REPLACE INTO'
syntax.

On an aside, I was recently asked to convert this MySQL code to PGSQL code
for the GeekLog project.  I gave up:

REPLACE INTO $table ($fields) SELECT $values FROM $tablefrom;

Evil!

Chris