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?
-
Please give more data about your problemKarthik– Karthik2010-04-19 11:13:33 +00:00Commented Apr 19, 2010 at 11:13
-
It's better to write like this one: DECLARE p_country VARCHAR; p_country : = ''; SELECT p_country;user307880– user3078802010-04-19 11:18:59 +00:00Commented Apr 19, 2010 at 11:18
-
What's wrong with the syntax.user307880– user3078802010-04-19 11:20:06 +00:00Commented Apr 19, 2010 at 11:20
Add a comment
|
3 Answers
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.
2 Comments
user307880
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.
Frank Heikens
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.
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';
1 Comment
user307880
But I want to declare a variable out of the function. Is it possible?
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;
