Can Postgres do this? (Specifically without a function() construct)?

Question

I have some oracle calls that I am porting. Today I came across this code which looks like "procedural" language, but is not declared in a function or anything... My question is: Can postgres handle this in this form? What form does this need to be in?

DECLARE
BEGIN
   IF :start_time IS NULL OR
      :start_date IS NULL OR
      :end_time IS NULL OR
      :end_date IS NULL  THEN
         INSERT INTO ARPSPACE_AVAILABILITY
            (ARP_ARPSPACE_NM, ASA_TIME_ID, ASA_START_DT, ASA_END_DT)
         SELECT :arpspace_name,
                 1,
                 ASP.ASP_START_DT,
                 ASP.ASP_STOP_DT
           FROM ASP 
          WHERE EXISTS
             (SELECT ARP.ARP_ARPSPACE_NM
                FROM ARPSPACE ARP
               WHERE ARP.ARP_ARPSPACE_NM = :arpspace_name);
   END IF;
END;

Frank Heikens · Accepted Answer · 2010-03-23 21:43:06Z

2

No, not yet without declaring a function. In version 9.0 (soon in Beta) this will be possible: http://developer.postgresql.org/pgdocs/postgres/sql-do.html

answered Mar 23, 2010 at 21:43

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

Comments

Justin Ethier · Accepted Answer · 2010-03-23 21:54:38Z

1

Short answer is yes, if you can declare a function. See plpgsql from the manual for details.

edited Mar 23, 2010 at 21:54

answered Mar 23, 2010 at 20:57

Justin Ethier

135k53 gold badges235 silver badges290 bronze badges

1 Comment

Frank Heikens Over a year ago

Not yet possible, you have to wait for 9.0

Matthew Wood · Accepted Answer · 2010-03-24 14:46:05Z

1

Why not do this (assuming :labels are prepared query parameters)?

     INSERT INTO ARPSPACE_AVAILABILITY
        (ARP_ARPSPACE_NM, ASA_TIME_ID, ASA_START_DT, ASA_END_DT)
     SELECT :arpspace_name,
             1,
             ASP.ASP_START_DT,
             ASP.ASP_STOP_DT
       FROM ASP 
      WHERE EXISTS
         (SELECT ARP.ARP_ARPSPACE_NM
            FROM ARPSPACE ARP
           WHERE ARP.ARP_ARPSPACE_NM = :arpspace_name)
      AND  (:start_time IS NULL OR
            :start_date IS NULL OR
            :end_time IS NULL OR
            :end_date IS NULL);

answered Mar 24, 2010 at 14:46

Matthew Wood

16.5k6 gold badges49 silver badges35 bronze badges

Collectives™ on Stack Overflow

Can Postgres do this? (Specifically without a function() construct)?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related