Postgres IF on variable

Question

I'm in the process of learning postgres, I've already found a work around to this problem but I wanted to ask the community if something like this is even possible, maybe my syntax is just off.

DO $$ BEGIN
    IF :MODIFYBY IS NOT NULL THEN
        UPDATE User SET ModifyBy = :MODIFYBY WHERE UserId = :USERID;
        UPDATE Profile SET ModifyBy = :MODIFYBY WHERE UserId = :USERID;
    END IF;
END $$;

Receiving

syntax error at or near ":"

as :MODIFYBY is a parameter to this sql.

How can I test if a parameter is null?

Note: Running on PostgreSQL 9.6

Update:

It is possible my terminology is not correct. The full sql statement is this

BEGIN;

    UPDATE User
       SET Email = :EMAIL         
          ,ModifyDate = now() at time zone 'utc'
    WHERE
        UserId = :USERID;

    UPDATE Profile
       SET FirstName = :FIRSTNAME          
          ,LastName = :LASTNAME          
          ,ModifyDate = now() at time zone 'utc'
    WHERE
        UserId = :USERID;

    DO $$ BEGIN
        IF :MODIFYBY IS NOT NULL THEN
            UPDATE User SET ModifyBy = :MODIFYBY WHERE UserId = :USERID;
            UPDATE Profile SET ModifyBy = :MODIFYBY WHERE UserId = :USERID;
        END IF;
    END $$;

COMMIT;

I added the DO $$ BEGIN and END $$; to get the IF statement to work...

There is no IF in (pure) SQL. But, you can add your condition to the WHERE clause. — wildplasser
– wildplasser, Commented Jun 21, 2017 at 19:22
where is the parameter definition? Im not sure if you can use : for parameter names. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jun 21, 2017 at 19:24
@wildplasser there is IF in anonymous block. I guess the op is confusing with parameters from a driver implementation like jdbc. — Michel Milezzi
– Michel Milezzi, Commented Jun 21, 2017 at 19:32
Variables don't use : in PL/pgSQL: postgresql.org/docs/current/static/plpgsql-declarations.html — user330315
– user330315, Commented Jun 21, 2017 at 19:58

Juan Carlos Oropeza · Accepted Answer · 2017-06-21 19:31:12Z

3

I think your problem is with the use of : parameters in a function doesn't use it.

This is an example of a function using IF

CREATE OR REPLACE FUNCTION traffic.check_distance(
    int_route_source_id bigint,
    num_distance_geo numeric)
RETURNS boolean AS
$BODY$
DECLARE
    bol_route_error boolean = false;
    num_distance_rto numeric;

BEGIN

    -- CALCULATE ROUTE DISTANCE
    SELECT INTO num_distance_rto 
    ....

    --RAISE DISTANCE ALARM 
    IF num_distance_rto > 3.5 * num_distance_geo THEN
        UPDATE traffic.Route_Sources        
        SET 
            IsValid = FALSE,
            result = '3.5x MUY LARGO'
        WHERE 
            route_source_id = int_route_source_id;   
        bol_route_error = true;
    END IF;

    RETURN bol_route_error;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION traffic.check_distance(bigint, numeric)
  OWNER TO postgres;

answered Jun 21, 2017 at 19:31

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

2 Comments

Layser Over a year ago

I think whats going on here is my anonymous function doesn't see or cannot take parameters from outside its boundaries, if my parameters are :EMAIL, :USERID, :FIRSTNAME, :LASTNAME, :MODIFYBY

bitifet Over a year ago

But this creates a function: It doesn't execute the code straightly and it leaves the function created which is not the goal...

Collectives™ on Stack Overflow

Postgres IF on variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related