how to declare variable in PostgreSQL

Question

I try to declare a variable in a code like this, but it's doesn't work. Can you tell me what's the problem? ERROR: syntax error at or near "VARCHAR" LINE 2: p_country VARCHAR;

DECLARE p_country VARCHAR; p_country : = ''; SELECT p_country;

Answer 1

Within a PL/pgSQL function you can declare variables like this:

CREATE FUNCTION identifier (arguments) RETURNS type AS '
  DECLARE

     -- Declare an integer.
    subject_id INTEGER;

     -- Declare a variable length character.
    book_title VARCHAR(10);

      -- Declare a floating point number.
    book_price FLOAT;

  BEGIN
    statements
  END;
' LANGUAGE 'plpgsql';

Source: http://www.commandprompt.com/ppbook/x19832

Answer 2

Create a new setting in postgresql.conf for custom_variable_classes:

custom_variable_classes = 'var'

Reload the config, you now have the variabele "var" available in all your databases.

To create the variable p_country, just use SET:

SET var.p_country = 'US';
SELECT current_setting('var.p_country') AS p_country;

It's not a beauty, but it works.