PL/pgSQL variable percieved as column

Question

I have a problem with my Postgres and it looks like a simple one. I have done my research but I have not seen anything similar online and would like some clarification:

This is done inside a function, here is the whole code:

BEGIN
    IF($5 IS NOT NULL) THEN
        BEGIN
            INSERT INTO "PushDevice"("DeviceId","PushNotificationId", "pushId","deviceType",sound)
            SELECT DISTINCT  d.id, $4,d.pushid,d.type,d.sound FROM "Device" d  inner join "DeviceLocation" dl ON  d.id = dl."DeviceId" 
            WHERE dl."FIPScode" in (select "FIPScode" from "CountyFIPS"  where "stateCode"=$5) AND dl."AppId"=$2 AND d.pushId is not null and d.pushId <>'' and d.pushId<>'1234-5678-9101-2345-3456' and d."isTest"=$3 and d."enableNotification"=TRUE and dl."isDeleted"=0
            AND NOT EXISTS (SELECT 1 FROM "PushDevice" t where t."DeviceId"=d.id AND t."PushNotificationId"=$4);
        END;
    ELSE 

        DECLARE "epiCentre" VARCHAR := NULL;
                        magnitude FLOAT = NULL;
        BEGIN
            SELECT polygon INTO "epiCentre" from alert  where id=$1 and "disablePush"=FALSE;
        END;

        IF("epiCentre" IS NOT NULL) THEN
        BEGIN
                INSERT INTO "PushDevice"("DeviceId","PushNotificationId", "pushId","deviceType","sound")
                SELECT DISTINCT  d.id, $4,d."pushId",d.type,d.sound FROM "Device" d  inner join "DeviceLocation" dl   ON  d.id = dl."DeviceId"
                WHERE  dl."AppId"=$2 AND d."pushId" is not null and d."pushId" <>'' and d."pushId" <>'1234-5678-9101-2345-3456' and d."isTest" =$3  and ST_Distance_Sphere(ST_GeometryFromText("epiCentre"), ST_GeometryFromText(geoPoint))<=d.radius * 1609.344 and magnitude>= d.magnitude and d."enableNotification"=1 and dl."isDeleted"=0
                AND NOT EXISTS (SELECT 1 FROM "PushDevice" t where t."DeviceId"=d.id AND t."PushNotificationId"=$4);
        END;
        END IF;

        RETURN QUERY SELECT pd.* FROM  "PushDevice" pd
        WHERE pd."PushNotificationId" =$4 and pd."sentAt" is null;

END IF;
END;

The problem is here specifically:

DECLARE "epiCentre" VARCHAR := NULL;
                        magnitude FLOAT = NULL;
        BEGIN
            SELECT polygon INTO "epiCentre" from alert  where id=$1 and "disablePush"=FALSE;
        END;

        IF("epiCentre" IS NOT NULL) THEN

With error:

Procedure execution failed
ERROR:  column "epiCentre" does not exist
LINE 1: SELECT ("epiCentre" IS NOT NULL)
                ^
QUERY:  SELECT ("epiCentre" IS NOT NULL)
CONTEXT:  PL/pgSQL function "GetDevicesForPush... line 18 at IF.

So somehow the IF statement perceives epiCentre as column instead of value. And it does not even know it exists although I specifically declared it above.

Any thoughts?

Please, *always* include the function header, which is an integral part of the function. — Erwin Brandstetter, Aug 08 '14 at 15:06

score 0 · Accepted Answer · answered Aug 08 '14 at 10:26

0

I think you have to many BEGIN-END statements. The declaration of epiCentre is only valid to the first END. And the IF is after that. Therefore I would use on Block for the whole ELSE part.

http://www.postgresql.org/docs/8.3/static/plpgsql-structure.html

answered Aug 08 '14 at 10:26

Jörg Mäder

677
4
13

IT was close. Note exactly that. I declared the vars as the first statement of my query and it worked. Thanks man – Martino Aug 08 '14 at 10:36

score 0 · Answer 2 · edited May 23 '17 at 11:57

As you have found yourself already that DECLARE must be placed before BEGIN of a each block.

More importantly, you do not need multiple blocks here at all. And you don't need a variable either. Use this simpler, safer and faster form:

CREATE function foo(...)
   RETURNS ... AS
$func$
BEGIN
   IF($5 IS NOT NULL) THEN
      -- no redundant BEGIN!
      INSERT INTO  ... ;
      -- and no END!
   ELSIF EXISTS (SELECT 1 FROM alert
                 WHERE  id = $1
                 AND    "disablePush" = FALSE
                 AND    polygon IS NOT NULL   -- only if polygon can be NULL
                ) THEN
      INSERT INTO ... ;

      ...

   END IF;
END
$func$ LANGUAGE plpgsql;

More Details:

PL/pgSQL checking if a row exists - SELECT INTO boolean

PL/pgSQL variable percieved as column

2 Answers2