1

Is there any significant difference (in performance for example) between character varying vs. text as types for db function parameters? I have 2 usage examples:

Example 1:

CREATE OR REPLACE FUNCTION func1(table_name <character varying|text>)
  RETURNS integer AS $$
DECLARE
  RESULT integer;
BEGIN
  EXECUTE 'SELECT COUNT(*) FROM ' || table_name INTO result;
  RETURN result;
END; 
$$ LANGUAGE plpgsql;

Example 2:

CREATE OR REPLACE FUNCTION func2(filter_value <character varying|text>)
  RETURNS integer AS $$
DECLARE
  RESULT integer;
BEGIN
  SELECT COUNT(*) INTO RESULT FROM <some_table> WHERE <some_column> = filter_value;
  RETURN result;
END; 
$$ LANGUAGE plpgsql;

NOTE in the 2nd example the some_column type is text if filter_value is of type character varying and vice versa

  • No difference at all. Neither in performance nor in memory. In general using text is easier as all unknown string literals are assumed to be text – a_horse_with_no_name Feb 14 at 9:08
  • Example1 - I think it doesn't matter what datatype is used. Example2 - the same datatype which <some_column> have is recommended (but in general it doesn't matter too). – Akina Feb 14 at 9:09
1

When it comes to performance, these data types are exactly the same, down to the implementation in C.

If in doubt, use text, because that is the preferred type of its type class, and the data type resolution rules for functions will resolve all unknown strings to text implicitly. With character varying you often have to use explicit type casts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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