3

I try to declare a variable in a code like this:

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

But it's doesn't work:

ERROR:  syntax error at or near "VARCHAR"
LINE 2:  p_country VARCHAR;

Can you tell me what's the problem?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user307880
  • 59
  • 1
  • 2
  • 5

3 Answers3

5

Create a new setting in postgresql.conf for custom_variable_classes:

custom_variable_classes = 'var'

Reload the config, you now have the variable "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.

devoured elysium
  • 101,373
  • 131
  • 340
  • 557
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • It's interesting. But the poblem is that I make queries to the remote server. I don't think I'll be allowed to edit postgresql.conf. – user307880 Apr 20 '10 at 06:41
  • You could ask them to add this in the configuration, it aint rocket science. You could also use a temp table to put your variables in. – Frank Heikens Apr 20 '10 at 08:03
3

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

Wolph
  • 78,177
  • 11
  • 137
  • 148
0

PL/pgSQL

I had the same problem as you. Turns out, the DECLARE statement is something from an entire different language PL/pgSQL. See https://www.postgresql.org/docs/14/plpgsql.html

PostgreSQL

To achieve this with PostgreSQL, you can do the following:

WITH myconstants (p_country) as (
   values ('')
)

SELECT p_country FROM myconstants;
kcid42
  • 50
  • 8