Sorry, this is probably a dumb mistake on my part. New to Postgres. Running
8.0 on Linux.
The array_append below is giving me a syntax error, and I have no idea what
I am doing wrong.
Also as a side note is there a straightforward way to get a value from a
dynamic query into a local variable? The loop construct at the end is the
only way I know how...
CREATE OR REPLACE FUNCTION "property"."get_score_type_id_from_url" (p_url
varchar) RETURNS integer AS
$body$
/* returns a score_type_id given a filter url fragment */
declare
v_parsed text[];
v_query_array text[] = '{}';
v_array_index integer = 1;
rec record;
v_value integer;
begin
if (char_length(p_url) = 0 or p_url is null) then
return 1;
end if;
v_parsed := string_to_array(p_url,'/');
FOR rec IN
SELECT * FROM property.filteritem ORDER BY filter_group_id, sort_order
LOOP
if (rec.item_name = v_parsed[v_array_index]) then
v_value = rec.item_field_value;
v_array_index = v_array_index + 1;
else
v_value = 0;
end if;
array_append(v_query_array,(rec.item_field_name || '=' ||
v_value::text)::text);
END LOOP;
/* todo: doesn't need to be a loop here */
FOR rec IN EXECUTE
'SELECT score_type_id FROM array_to_string(v_query) WHERE ' ||
array_to_string(v_parsed,' AND ')
LOOP
return rec.score_type_id;
END LOOP;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
;
