in fact i'm trying to do the following thing:Hi,
i have a character varying variable and i concatenate with some other variable, using the '||' operator.
the result of this concatenation should be the name of a column in my table.
however i don't know how to tell that this new concatenated string is a column name.
how to do it ?
thanks.
select id, theme_ || $1 from themes;
and based on the parameter my stored procedure has received, it should create something like that:
select id, theme_eng from themes;
if $1 = 'eng'
I think i'm a good way, but i still have a problem with the performance.
when i call my stored procedure, it can take 0.1 s to 3.5 seconds to execute it on local computer.
How can i improve it ?
here is my stored procedure:
CREATE OR REPLACE FUNCTION sewe.get_category_and_amount(character varying)
RETURNS SETOF category_amount AS
$BODY$
DECLARE
inLanguage ALIAS FOR $1;
outCategoryAndAmount category_amount;
Lang character varying :='';
BEGIN
IF inLanguage = null OR inLanguage = '' THEN
Lang := 'eng';
ELSE
Lang := inLanguage;
END IF;
FOR outCategoryAndAmount IN
EXECUTE 'SELECT id, theme_name_' || Lang || ' FROM themes WHERE parent_theme IS NULL ORDER BY theme_name_' || Lang || ' ASC'
LOOP
RETURN NEXT outCategoryAndAmount;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;