Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
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 '13 at 11:19
add comment

2 Answers

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.

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.

share|improve this answer
    
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 '13 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. –  Erwin Brandstetter Sep 11 '13 at 11:34
add comment

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.

share|improve this answer
    
Hi, I have added the full function in to the original question. –  kds Sep 9 '13 at 12:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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