4

When I assign a variable with

result := title || '', by '' || author;

it is taking more time (approx. 15 sec) to run the function.
However, when I assign the variable with

result = title || '', by '' || author;

it takes only 133ms.

Why is it taking more time for the first scenario? And what is the reason behind this?

Full function is given below.

CREATE OR REPLACE FUNCTION myschema.fn_get_res_no(reservation_no character varying)
  RETURNS character varying AS
$BODY$  

DECLARE  
    emd_status_firstcall varchar(2);  
    emd_status_secondcall varchar(2);
    emd_status      varchar(6);  
BEGIN  
    SELECT firstwscomplete, secondwscomplete
    INTO emd_status_firstcall, emd_status_secondcall
    FROM myschema.mytable
    WHERE respkgconfirmid = reservation_no;

    emd_status = emd_status_firstcall || ', ' || emd_status_secondcall;
    RETURN emd_status ;  

END;  
$BODY$
  LANGUAGE plpgsql VOLATILE;
1
  • If you used dollar quoting (at least for the function body, like CREATE FUNCTION bla() RETURNS void AS $body$ ... $body$ LANGUAGE plpgsql; then you wouldn't need doubled quotes in your code. Just an idea.
    – dezso
    Sep 9, 2013 at 11:19

2 Answers 2

7

This question concerns PL/pgSQL exclusively, where := is the assignment operator. It has no place in pure SQL.

Using = instead of := in PL/pgSQL is an undocumented legacy feature, which should not be used.
:= is the documented assignment operator in PL/pgSQL..
More details about that in this related question on SO.

Update: Since Postgres 9.4 = is also documented as plpgsql assignement operator.

I have never seen = performing faster than :=. They should be identical. I fact, I tested your exact code in Postgres 9.1 and 9.2 and found no difference in performance whatsoever.
You may be mis-interpreting caching effects. Always run EXPLAIN ANALYZE a couple of times to populate the cache and get comparable results.

Finally, you could replace your function with this much simpler and faster one:

CREATE OR REPLACE FUNCTION myschema.fn_get_res_no(reservation_no varchar)
  RETURNS varchar AS
$BODY$  
BEGIN  
   RETURN (
      SELECT firstwscomplete || ', ' || secondwscomplete
      FROM   myschema.mytable
      WHERE  respkgconfirmid = reservation_no
      );
END  
$BODY$ LANGUAGE plpgsql;

Or even use a plain SQL function:

CREATE OR REPLACE FUNCTION myschema.fn_get_res_no(reservation_no varchar)
  RETURNS varchar AS
$BODY$  
   SELECT firstwscomplete || ', ' || secondwscomplete
   FROM   myschema.mytable
   WHERE  respkgconfirmid = reservation_no);
$BODY$
  LANGUAGE sql;

Assignments in PL/pgSQL

It is comparatively expensive to make assignments in PL/pgSQL, which is a very simple Ada-like procedural language. Every assignment is executed with a (very simple) SELECT command, which is more expensive than in other fully fledged procedural languages.

PL/pgSQL excels as glue for SQL commands, when you need some procedural elements.

2
  • Hi Erwin, Thanks for the comments. However, I face this issue in the PostgreSQL 8.2. I wonder this issue has been fixed in the newer versions.
    – kds
    Sep 11, 2013 at 4:44
  • @kds: There is a very long list of things that have been fixed since Postgres 8.2. I strongly suggest you find a way to upgrade to a current version of Postgres. Consider the versioning policy. Next time also please remember to provide your version number with your question. Sep 11, 2013 at 11:34
0

Well as far as I know "=" is not an assignment operator in PL/pgSQL, it is a comparison operator. If this does not explain the issue then we'd best see the complete code.

1
  • Hi, I have added the full function in to the original question.
    – kds
    Sep 9, 2013 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.