If-statement With String Containing The Condition

This question is about Postgresql 8.3. I've got a table with a field containing conditions like 'lastcontact is null'. In code, I want to loop through this table and for each recor

Solution 1:

From the comments I finally think I understand. You need dynamic SQL:

CREATEOR REPLACE FUNCTION foo(lastcontact timestamptz)
  RETURNS void AS
$func$
DECLARE
   myrec  record;
   mycond boolean;
BEGINFOR myrec INSELECT*FROM tabel ORDERBY colorlevel, volgnummer
LOOP
    IF myrec.conditie ~~'%lastcontact %'THEN-- special case for input param
        myrec.conditie := replace (myrec.conditie
                        , 'lastcontact '
                        , CASEWHEN lastcontact ISNULLTHEN'NULL 'ELSE''''|| lastcontact::text ||''' 'END);
    END IF;

    EXECUTE'SELECT '|| myrec.conditie ||' FROM tabel'INTO mycond;

    IF mycond then
        RAISE NOTICE 'Condition % is true', myrec.conditie;
    ELSE
        RAISE NOTICE 'Condition % is false', COALESCE(myrec.conditie, 'NULL');
    END IF;
END LOOP;

END
$func$  LANGUAGE plpgsql;

Be aware, however, that this setup is wide open for SQL injection. Only use verified input. A function works in PostgreSQL 8.3 as well (no DO statements, yet).

You cannot refer to parameters inside dynamic SQL (EXECUTE statement). You have to put the value into the query string.

In PostgreSQL 8.4 or later you have the superior commodity of the USING clause. Alas, not in version 8.3. You should consider upgrading if you can.

I put in a workaround for your old version. You have to take special care of the NULL value.