I have a column in a table by name color which has distinct values like "Red", "Green", "Blue", "Yellow".
I need to know how to create the variable names dynamically by their distinct values.
i.e each variable name will be the value.
In the above case four variable names to be created i.e."Red", "Green", "Blue", "Yellow".
to be clear
Basically it is declaring the variables in the declaring section where the variables names can be dynamic
Assuming that the context is the plpgsql language, it's not possible to have dynamic contents in the DECLARE section.
Anyway, dynamically created variables are rarely used even when they're technically possible. In other interpreted languages, the common way to address the need for a variable number of variables is to use a map (perl) or an array (php) indexed by names as is they were variable names.
In plpgsql you may use the hstore type as the closest equivalent.
Example:
DECLARE
vars hstore:=hstore('');
BEGIN
-- assign a pseudo-variable with name='Blue' and value='abc'
vars:=vars||'Blue=>abc';
-- load values from a query selecting names and associated values
for color,val in select * from colors
loop
vars:=vars||(color=>val::text);
end loop;
-- Get the value of the pseudo-variable for 'Red', assuming it came out
-- in the query's results
raise notice 'the value for Red is: %', vars->'Red';
END;
The main drawback compared to real variables is that there's only one type for the content: text. The values need to be casted dynamically when text is not suitable.
