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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

    emd_status_firstcall varchar(2);  
    emd_status_secondcall varchar(2);
    emd_status      varchar(6);  
    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 ;  

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

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
      SELECT firstwscomplete || ', ' || secondwscomplete
      FROM   myschema.mytable
      WHERE  respkgconfirmid = reservation_no
$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
   SELECT firstwscomplete || ', ' || secondwscomplete
   FROM   myschema.mytable
   WHERE  respkgconfirmid = reservation_no);

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

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

Your Answer


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.