PostgreSQL trigger with a condition

Question

Say, I have the following in MyBatis mapper for a postgres table:

    <select id="selectValues" parameterType='int' resultType="SomeType">
        select foo from bar where baz=#{qux}
    </select>

A list of SomeType values can be returned from the table. But I'd like to check with a help of trigger, if the returned list is empty and, if so, give it a null value. It would probably look like:

CREATE TRIGGER mytrigger AFTER select ON bar FOR EACH STATEMENT EXECUTE PROCEDURE trigger_after_select ();

CREATE FUNCTION trigger_after_select () RETURNS trigger AS ' 
BEGIN 
if (select count(*) from bar)=0
then return NULL; 
...

I wonder if I'm moving in the right direction and if someone could tell how the rest of the trigger would look like (if the beginning looks suitable). An advice would be helpful, thanks in advice.

user330315 · Accepted Answer · 2012-05-21 21:48:37Z

3

There is no "SELECT" trigger in Postgres.

If you change your SQL to:

select foo from bar where baz=#{qux}
union all
select null where not exists (select 1 from bar where baz=#{qux})

then you'll get the same effect. It will return a NULL value if there is no row with that value for the column baz.

(Disclaimer: I don't know MyBatis, so I don't know if it's possible to specify such a statement)

answered May 21, 2012 at 21:48

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL trigger with a condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related